Power Query in Excel: het stuk dat je al hebt

Power Query is het onderdeel van Excel waarmee je gegevens ophaalt en opschoont, en je hebt het al. Je legt stappen vast, zoals een map met exports inlezen, bestanden samenvoegen en kolommen hernoemen. De volgende maand voert Power Query dezelfde stappen opnieuw uit, met één klik. En het is dezelfde techniek als in Power BI.

In dit artikel lees je wat Power Query doet, hoe je er een map met maandexports mee inleest, wat de valkuilen zijn en waar het ophoudt. Hoe Power Query past in de overstap naar Power BI, staat in de gids van Excel naar Power BI.

Wat Power Query doet

Veel controllers doen elke maand hetzelfde. Een export uit de boekhouding openen, kolommen verwijderen, datums goed zetten, het rekeningschema erbij zoeken met VERT.ZOEKEN, en het geheel in het rapportagebestand plakken. Een uur werk, of meer, en elke keer een kans op een fout.

Power Query doet dat werk voor je. Je doet het één keer, en Power Query onthoudt elke stap. De volgende maand zet je de nieuwe export in de map, klikt op Alles vernieuwen, en Power Query herhaalt alles. Zonder plakken, zonder vergeten stap.

In Excel voor Windows vind je het onder Gegevens, Gegevens ophalen. Daar kies je de bron: een bestand, een map, een database of een webpagina.

Een voorbeeld: twintig maandexports

Noordkade Techniek, het fictieve installatiebedrijf uit de demo, heeft voor elke maand een export van het grootboek, twintig bestanden in één map. Zo leest Power Query ze in.

1. Bron: de map. Kies Uit map, of bij bestanden in SharePoint de SharePoint-map. Power Query ziet alle bestanden.
2. Bestanden samenvoegen. Power Query plakt ze onder elkaar, op basis van het eerste bestand als voorbeeld.
3. Gegevenstypen instellen. Datums als datum, bedragen als getal. Let op het decimaalteken bij Nederlandse exports.
4. Rekeningschema samenvoegen. Met Query’s samenvoegen koppel je elke boeking aan een rubriek, zonder VERT.ZOEKEN.
5. Overbodige kolommen weg. Alleen wat je nodig hebt, dan blijft het bestand snel.

Elke stap staat in het rijtje Toegepaste stappen. Klik je op een stap, dan zie je de gegevens zoals ze er op dat moment uitzagen. Dat maakt fouten zoeken makkelijk.

Vijf tips die het verschil maken

1. Geef stappen een naam. “Kolommen verwijderd 3” zegt niemand iets. “Alleen benodigde kolommen” wel. Rechtsklik op de stap om hem te hernoemen.
2. Gebruik een map, geen bestand. Wie naar een vast bestand verwijst, moet elke maand het pad aanpassen. Een map pakt vanzelf het nieuwe bestand mee.
3. Zet bronnen in SharePoint, niet op je laptop. Dan werkt het ook als een collega ververst, en later in Power BI zonder extra stappen.
4. Let op het decimaalteken. Nederlandse exports gebruiken een komma. Stel bij het wijzigen van het type de landinstelling in, anders wordt 14536,94 ineens 1,4 miljoen.
5. Laad naar een tabel, niet naar een tabblad vol formules. Laat Power Query het rekenwerk doen, en bouw je rapport op de tabel die eruit komt.

Excel en Power BI: dezelfde techniek

Power Query zit in Excel en in Power BI, en het werkt hetzelfde. Dezelfde knoppen, dezelfde stappen, dezelfde taal eronder, M. Een query die je in Excel hebt gebouwd, kun je kopiëren en in Power BI plakken.

Wie in Excel al met Power Query werkt, heeft het moeilijkste deel van Power BI dus al onder de knie. Het verschil zit in wat er daarna gebeurt. In Excel landt de data in een tabblad, en reken je verder met formules en draaitabellen. In Power BI landt de data in een model, met relaties tussen tabellen en berekeningen die overal hetzelfde werken.

Een tweede voorbeeld: de begroting

De begroting van Noordkade staat in Excel zoals controllers hem graag maken: een regel per kostenplaats en rubriek, met de twaalf maanden als kolommen en een totaal erachter. Prettig om in te typen, lastig om mee te rekenen.

Power Query zet hem om zonder dat de controller iets anders hoeft te doen. Verwijder de kolom met het totaal, selecteer de twaalf maandkolommen en draai ze om naar regels, met de functie die in de Engelse versie Unpivot heet. Daarna heb je een regel per kostenplaats, rubriek en maand, precies zoals het grootboek. Nu kun je begroting en realisatie naast elkaar zetten.

De volgende keer dat de begroting wordt herzien, klik je op Alles vernieuwen. De controller blijft werken in zijn eigen vorm.

Moet je M leren?

Achter elke stap staat een regel code in de taal M. Je hoeft die niet te kennen om te beginnen. De meeste stappen klik je in elkaar, en Power Query schrijft de code. Na een tijdje is het handig om de code te kunnen lezen, bijvoorbeeld om een pad aan te passen of een stap te herhalen. In de geavanceerde editor zie je de hele query.

Leer M pas als je tegen iets aanloopt wat je niet kunt klikken. Dat duurt meestal langer dan je denkt.

Waar Power Query ophoudt

Power Query lost het ophalen op, niet alles wat daarna komt. Drie grenzen kom je in Excel tegen.

• Meerdere tabellen samen analyseren. In Excel voeg je tabellen vaak samen tot één grote tabel. Dat werkt tot het te groot wordt of totalen dubbel tellen. Een model met relaties lost dat op.
• Delen. Een Excel-bestand met Power Query blijft een bestand. Wie het opent, moet het verversen, en krijgt een eigen versie.
• Automatisch verversen. In Excel ververst iemand met de hand. In Power BI kan dat volgens een schema, bijvoorbeeld elke nacht.

Excel heeft zelf ook een datamodel, onder de naam Power Pivot, met relaties tussen tabellen. Dat is een goede tussenstap. Voor delen en automatisch verversen is Power BI de logische volgende stap.

Wat ik vaak zie misgaan

• Een vast bestandspad op een laptop. Werkt alleen voor degene die het bouwde. Zet de bron in SharePoint en gebruik een map.
• Alles in één query. Twintig stappen voor drie bronnen door elkaar. Maak liever per bron een query, en voeg ze aan het eind samen.
• Daarna toch weer plakken. Power Query leest alles in, en vervolgens wordt het resultaat met de hand in een rapportagebestand gezet. Bouw het rapport op de tabel die Power Query maakt.

Wat je nu kunt doen

1. Kies één export die je elke maand opnieuw verwerkt. Zet de bestanden van een paar maanden in één map.
2. Lees de map in met Power Query en leg je gebruikelijke stappen vast. Geef elke stap een naam.
3. Zet volgende maand het nieuwe bestand in de map en klik op Alles vernieuwen. Hoe het eruitziet als die gegevens in een model staan, zie je in de demo.