Power Query verandert jouw manier van werken in Excel

Power Query bestaat pas sinds 2010, maar de naam is alweer verouderd. In Office 365 en Power BI heet deze functionaliteit tegenwoordig ‘Ophalen en transformeren’.  Met die naamswijziging is Power Query een vast onderdeel geworden van Excel 2016. Heb je een andere versie van Excel, dan vind je de juiste invoegtoepassing van Power Query op deze website.

Power Query met stip op de eerste plaats

Power Query is een ETL-gereedschap. ETL staat voor Extract, Transform and Load. Dat betekent het ophalen van een tabel uit bijvoorbeeld een database, het aanpassen van die tabel aan jouw wensen en laden van die tabel in Excel (of Power Pivot). En lees voor database bijvoorbeeld ook Excel of tekstbestanden. Omdat Power Query alle stappen in een script vastlegt, is het gebruik van de knop Verversen voldoende om alle handelingen in het script opnieuw uit te voeren. Omdat transformaties alles wat je maar bedenkt omvat, ben je in een keer van heel veel, vaak handmatig, werk af. Daarom is voor mij Power Query met stip op de eerste plaats terecht gekomen in mijn Excel gereedschapskist.

Werken met Excel Power Query

Laat ik een populair voorbeeld geven van het werken met Power Query. Stel je voor, je krijgt iedere maand een tekstbestand met drie kolommen: een datum, een productnummer en de verkochte aantallen van de vorige maand. Wat je wilt is dat de nieuwe informatie automatisch wordt toegevoegd aan de tabel in Excel, zodat je in een handeling een jaar of een meerjarig overzicht maakt. Je doet overigens hetzelfde met informatie in Excel bestanden, maar de werkwijze daarvoor verschilt.

In afbeelding 1 zie je een van de tekstbestanden in Excel 2016: de informatie over de maand Januari. Je gebruikt een map waarin je alleen de tekstbestanden plaatst (afbeelding 2). Ieder maand voeg je het nieuwe bestand toe en druk je op de knop Verversen. Miraculeus wordt de informatie van de nieuwe maand aan de bestaande lijst toegevoegd. Wil je bepaalde maanden niet in de lijst hebben (bijvoorbeeld omdat het de maanden van vorig jaar zijn), dan haal je de niet gewenste bestanden gewoon uit de map.

De Query-editor openen

Je begint in het tabblad Gegevens, sectie Ophalen en transformeren, de knop Nieuwe Query. Je kiest uit het menu de optie Uit bestand, Uit map (afbeelding 3) en selecteer de map waar de bestanden staan. Je krijgt een venster, met de meta informatie van alle bestanden. Let even op, klik niet op de knop laden, maar op de knop Bewerken (afbeelding 4).

Het venster van de Query-editor opent. Richt je aandacht op de filterknopjes in de koprij van de tabel. Je ziet bij een aantal kolommen de vertrouwde filterknoppen, maar bij andere ook uitklapknoppen. Wat je doet is klikken op de uitklapknop van de kolom Content (afbeelding 5). Je krijgt een voorbeeld van de tabel die je kunt verwachten (afbeelding 6). Pas, indien nodig, het scheidingsteken aan naar puntkomma. Vervolgens krijg je een query, die vernoemd is naar de naam van de map.

De volgende stap is dat je de tabel verder aanpast

De eerste kolom met de bestandsnaam verwijder je. Daarvoor kies je in het tabblad Start voor Kolommen verwijderen, kolommen verwijderen (afbeelding 7).  Vervolgens zet je de kolom Datum in het juiste data type. Klik op de data type knop en kies voor Datum (afbeelding 8). Let ook op de data types van de overige twee kolommen. Verander het data type, indien nodig, in Tekst en Geheel getal. Je kiest in het tabblad Start voor de onderkant van de knop Sluiten en laden, de optie Sluiten en laden naar. Om de query af te sluiten klik je op de knop Laden (afbeelding 9). Uiteindelijk is de complete tabel terug te vinden in Excel (afbeelding 10). 

Power Query het meest krachtigste en het gebruiksvriendelijke gereedschap

Naar mijn mening is Power Query het meest krachtigste en het gebruiksvriendelijke gereedschap, dat momenteel in Excel bestaat. Het is echt de moeite waard om je in de mogelijkheden van Power Query te verdiepen.

Henk Vlootman geeft sinds 1994 Excel en Power BI trainingen aan managers over de gehele wereld. Vanaf 2013 ontving Henk jaarlijks de Microsoft MVP (Most Valuable Professional) award. In 2016 werd de MVP award voor de gebieden Excel en Data platform toegekend. Die laatste erkenning kreeg hij vanwege zijn werk met de Power BI gereedschappen. Henk is de auteur van “Excelmodellen voor financiële economische informatie” en “ExcelLeren, de Vlootman methode in de praktijk” en medeauteur van het “Praktijkboek Powerpivot in Excel” en “Handboek Power pivot”. Tijdens Excel en Power BI congressen en conferenties is hij een gewaardeerd spreker. Henk is voorzitter van de stichting Power BI gebruikersgroep Nederland.