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

5 stappen in Power BI (2) Modelleren en berekenen

29-05-2020 11:23

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

De echte waarde van Power BI

29-05-2020 11:19

Je wilt bij BI-projecten zo snel mogelijk naar de niveaus delen en visualiseren, waar de waardecreat...

Visualiseren, de kunst van het verleiden (1)

29-05-2020 11:16

Visualiseren in Excel en Power BI, deel 1: de rol van hiërarchieën.

Visualiseren, de kunst van het verleiden (2)

29-05-2020 07:42

Visualiseren in Excel en Power BI, deel 2: schakelen tussen hiërarchieën.

5 stappen in Power BI

15-05-2020 13:00

Webinar van Excel MVP Tony de Jonker ontsluit de geheimen van Power BI (1)

De 8 do's en 8 don'ts bij een doorstart

15-05-2020 12:09

Wordt er in uw bedrijf een doorstart overwogen of voorbereid? Dan is het verstandig onderstaande vui...

Betere rapportages met Excel en Power BI

08-05-2020 10:56

Excel MVP Tony de Jonker's tips om met Excel en Power BI uw financiële rapportages te versnellen en...

Supermarkten in Control

15-04-2020 11:54

Supermarkten zijn het nieuwe rolmodel van de anderhalvemeter economie. Wanneer voor duurzaamheid?

Vergroot uw kennis, online en ook weer klassikaal

15-04-2020 11:54

Uw veiligheid staat voorop. Alle cursussen zijn 100% Coronaproof. Indien een leslocatie gesloten is,...

Veel horecabedrijven in de problemen bij terugbeta...

15-04-2020 11:52

Veel bedrijven komen ondanks overheidsmaatregelen in zwaar weer, mede door de terugvallende omzet va...

Coronacrisis mag geen schuldencrisis worden

15-04-2020 11:33

"Alleen samen kunnen we de financiële schade beperken." Michel van Leeuwen, directeur en gerechtsdeu...

'Organisaties niet goed voorbereid op ransomware'

15-04-2020 09:30

Organisaties lopen flink risico om geraakt te worden door ransomware én de impact kan dan groot zijn...