Excel wordt niet alleen gebruikt voor het maken van rekenmodellen, maar ook voor data-analyse. Maar om dit te kunnen doen moeten de gegevens wel geschikt zijn. Als je dat elke maand opnieuw handmatig moet doen, word je daar niet vrolijk van. Ga geen macro’s maken, maar gebruik Power Query!
Stel je krijgt regelmatig updates van een csv-bestand als dit.
Dan weet je dat je aan de slag moet! Je kunt natuurlijk Tekst naar kolommen gebruiken waarmee je de data kunt verdelen over kolommen en daarna aanvullende handelingen uitvoeren als kolommen verwijderen, verplaatsen, sorteren en aanvullende berekeningen maken. Maar als je dit bestand dan een volgende keer krijgt, moet je dezelfde handelingen wéér uitvoeren. Vaak willen mensen hier dan een macro voor maken, maar met Power Query gaat dit veel handiger. En net als met een macro, kun je het steeds weer opnieuw gebruiken!
Ik start met een nieuw leeg werkblad. Weet je dat je dat heel snel kunt doen met Ctrl+n?
Je kiest dan op de tab Gegevens [Data] voor Gegevens ophalen [Get Data] ①. Je ziet dan een menu waarin je voor een grote hoeveelheid bronnen kunt kiezen om data binnen te halen in Excel. Bij ② zie je voor veel gebruikte bronnen aparte knoppen. Daar staat o.a. ook Uit tekstbestand/CSV bij: die gebruiken we in dit voorbeeld.
Hierna moet je opgeven waar je CSV-bestand staat. Vervolgens krijg je het volgende venster.
Bij ① zie je de data zoals ze in één kolom staan.
Bij ② zie je dat er al herkend wordt wat het scheidingsteken is om naar de volgende kolom te gaan: puntkomma.
Als je kiest voor ③ komt het als één kolom in Excel, dus je wilt de data eerst bewerken. Kies dus ④ Gegevens transformeren.
Je komt nu in het Power Query-venster. Daarin ga je de data in orde maken: de query maken.
Om meer ruimte te hebben kun je het queryoverzicht bij ① dichtvouwen.
Bij ② zie je de voorbeelddata. Bij elke stap die je uitvoert, zul je het resultaat hier zien.
Bij ③ zie je de naam van de query: die kun je eventueel aanpassen.
Bij ④ zul je de stappen zien die je gaat uitvoeren bij het bewerken van de gegevens. Vergelijk het met de stappen die je uitvoert met een macro. In dit voorbeeld zijn er al 2 stappen die automatisch zijn uitgevoerd.
Als eerste moeten de gegevens gesplitst worden. Kies ① Start > Kolom splitsen. We kiezen hier Op scheidingsteken [By Delimiter]. In het scherm dat volgt zie je dat voorstel voor de puntkomma weer. Vergeleken met Excels “Tekst naar kolommen” zie je al meer mogelijkheden. Het voorstel van de Puntkomma ② is juist en je wilt dat we elke puntkomma gebruikt wordt ③.
Het resultaat zie je direct in het voorbeeldvenster ①. De stap is ‘vertaald’ in de programmeertaal van Power Query ② (maar die hoeft je niet te leren ?).
De eerste rij bevat de kolomomschrijvingen, dus dat is de volgende stap. Kies De eerste rij als veldnamen gebruiken. Wederom zie je het resultaat direct.
Als je van de Invoicedate alleen de datum wilt hebben, kun je die ook splitsen op een spatie.
Kolommen die je niet wilt overnemen kun je verwijderen (selecteren en Delete). Of (over het algemeen beter) je selecteert de kolommen die je wilt behouden (klik op de eerste en Ctrl+klik op elke volgende) en kies dan Kolommen verwijderen > Andere kolommen (of gebruik hiervoor de rechtermuisknop).
Eventueel aanvullende dingen die je kunt uitvoeren:
- Als je kolomomschrijvingen wilt aanpassen dubbelklik je erop en typ je de nieuwe naam.
- Wil je de volgorde van kolommen wijzigen, sleep dan de kolomomschrijvng naar de nieuwe plaats.
Na elke actie zie je dat de TOEGEPASTE STAPPEN [APPLIED STEPS] verder zijn aangevuld.
In de kolom VatPercentage zie je 0 of 21 staan (geheel getal). Als je dat als een percentage wilt zien, zul je dit eerst door 100 moeten delen. Je kunt hiervoor de kolom transformeren met een berekening /100. Klik op de kolom om die te selecteren, tab Transformeren > Standaard > Delen ①. Vul in dat je het wilt delen door 100 ②.
De laatste stap die je eigenlijk altijd moet uitvoeren, is bij elke kolom controleren of die het juiste gegevenstype heeft. Je herkent dat aan het symbool ervoor ②. Als je het wilt wijzigen, kun je daar ook op klikken en het aanpassen ①.
Als laatste geef je aan hoe je de gegevens in Excel wilt hebben: Start > menu van Sluiten en laden > Sluiten en laden naar … ①. Globaal kun je kiezen voor een tabel of voor een draaitabel ②. Als je trouwens direct op de knop zelf klikt, zal gekozen worden voor het laden als een tabel.
Je ziet de gegevens in Excel ①: je hebt een query gemaakt ②.
Hoewel je een gegevenstype hebt aangegeven in Power Query zie dat dat hier allemaal in de getalnotatie Standaard. Dat moet je dus eventueel wel aanpassen.
Nu de kracht: volgende maand krijg je een nieuwe download. Je hoeft alleen maar die nieuwe download op te slaan op dezelfde plaats en met dezelfde naam. Daarna klik je met de rechtermuisknop in de (draai)tabel > Vernieuwen en je hebt direct de bijgewerkte versie!
Als de nieuwe download een andere naam heeft of als je achteraf iets aan de gegevens wilt wijzigen: je kunt de stappen van de query eenvoudig bewerken (rechtermuisknop). Zoals je ziet: geen macro nodig!
In een eerder verschenen blog over data uit een pdf halen zie je een ander voorbeeld van data geschikt maken voor verwerking in Excel. Ook als je van plan bent om Power BI te gebruiken, zul je kennis moeten hebben van Power Query, zoals je kunt lezen in de blog Power BI versus Excel.
Saskia Jacobsen is directeur-eigenaar van Toels-PC en helpt bedrijven en vooral hun gebruikers om slimmer te werken met de apps op hun pc.
Meer kunnen met Excel?
Online Excel Expert-training voor de accountant: leer nieuwe geheimen van Excel
Geef een reactie