1. Home
  2. Artikelen
  3. Power Query verandert jouw manier van werken in Excel

Power Query verandert jouw manier van werken in Excel


Microsoft heeft Power Query toegevoegd aan Excel, Power BI en SQL Server en ik schrijf er een blog over. Dan moet het wel een spectaculaire toepassing zijn, hoor ik je bijna denken. En ja, dat heb je goed gedacht. Ik ben er zeker van: Power Query verandert jouw manier van werken in Excel.

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 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.


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). 

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.


Gerelateerde artikelen voor financials

06-10-2020 14:26

Het waarderen van een bedrijf kan knap lastig zijn, zeker in een periode van crisis. We vroegen een...

30-09-2020 10:44

"Zorg dat je er heel snel bij bent." Ronald Molendijk, spreker op FM Dag 2020.

30-09-2020 10:40

Coronacrisis versnelt ontwikkeling online bedrijfsonderwijs met als voordeel maximale kennisoverdrac...

30-09-2020 10:31

Een geslaagde digitale transformatie is meer dan de digitalisering van bestaande processen, producte...

30-09-2020 08:49

Acht verschillende bedrijfsculturen: in welke werkt u?

08-09-2020 07:55

Sijthoff Media heeft opleidingsinstituut, community-bouwer en uitgeverij Alex van Groningen overgeno...

29-07-2020 07:46

Hoe zorg je ervoor dat jij en je team komen tot de best mogelijke beslissingen?

28-07-2020 08:15

Modelleren en berekenen, Power BI en DAX: webinar van Excel MVP Tony de Jonker.

27-07-2020 14:48

Wil je hufterproof Excel-modellen maken dan moet je een andere mindsetting van werken in Excel aanme...

27-07-2020 14:38

Data is het nieuwe goud, maar aggregaat berekeningen in Excel laten dat goud glanzen.

27-07-2020 14:24

Ik heb Excel nog nooit op een rekenfoutje betrapt. Het is de onkunde van de gebruiker die zorgt voor...

27-07-2020 13:27

Kijk voorbij de dagelijkse eisen en ontdek de onbekende meerwaarde van Excel.