Power Query: Gegevens importeren en transformeren als een pro

Als je wekelijks uren besteedt aan het downloaden van CSV-bestanden, het verwijderen van lege rijen, het opmaken van datums en het schrijven van complexe geneste formules om je gegevens klaar te maken voor analyse, dan werk je harder dan nodig is. Welkom bij Power Query—de krachtigste tool voor gegevensautomatisering die rechtstreeks in Microsoft Excel is ingebouwd.

Vaak aangeduid als "Gegevens ophalen en transformeren", stelt Power Query je in staat om verbinding te maken met vrijwel elke gegevensbron, de informatie op te schonen en te herstructureren, en deze in je spreadsheet te laden. En het beste van alles? Het slaat je stappen op. De volgende keer dat je nieuwe gegevens ontvangt, hoef je het handmatige werk niet te herhalen; je klikt simpelweg op Vernieuwen.

In deze uitgebreide gids onderzoeken we wat Power Query is, hoe je door de interface navigeert en doorlopen we een praktisch voorbeeld van het transformeren van een rommelige dataset naar schone, analyseklare informatie.

Wat is Power Query?

Power Query is een engine voor gegevensverbinding en -voorbereiding. In de wereld van databasebeheer staat dit proces bekend als ETL: Extract (Extraheren), Transform (Transformeren) en Load (Laden).

• Extract: Haal gegevens op uit verschillende bronnen (Excel-werkmappen, CSV- en tekstbestanden, webpagina's, SQL-databases of zelfs clouddiensten).
• Transform: Schoon gegevens op en herstructureer ze. Dit omvat het filteren van rijen, het wijzigen van gegevenstypen, het splitsen van tekst, het samenvoegen van tabellen en het maken van berekende kolommen.
• Load: Voer de gepolijste gegevens direct uit naar een Excel-tabel, een Draaitabel of het Power Pivot-gegevensmodel.

Van oudsher vertrouwden Excel-gebruikers op een combinatie van functies zoals TRIM, PROPER, SUBSTITUTE en VLOOKUP in combinatie met handmatig kopiëren en plakken om deze taken uit te voeren. Power Query vervangt deze tijdrovende workflow door een visuele, gebruiksvriendelijke interface.

Waarom je Power Query zou moeten leren

Als je nog twijfelt of je een nieuwe Excel-tool wilt leren, is hier de reden waarom het beheersen van Power Query een game-changer is voor je productiviteit:

• Geen programmeerkennis vereist: Hoewel Power Query draait op een formuletaal genaamd "M", kun je 95% van de gegevenstransformaties uitvoeren met alleen de knoppen op het lint en de rechtsklikmenu's.
• Automatisering zonder VBA: Je hoeft niet te weten hoe je macro's schrijft om repetitieve taken te automatiseren. Power Query onthoudt je "Toegepaste stappen" en past deze automatisch toe op nieuwe gegevens.
• Kan grote hoeveelheden gegevens aan: Excel heeft een fysieke limiet van 1.048.576 rijen. Power Query kan verbinding maken met datasets met miljoenen rijen, de gegevens op de achtergrond aggregeren en alleen de samengevatte resultaten in je spreadsheet laden.
• Niet-destructief bewerken: Power Query wijzigt je originele gegevensbron nooit. Het maakt een verbinding, leest de gegevens en voert een nieuw opgeschoonde kopie uit.

Aan de slag: De Power Query-editor

Om toegang te krijgen tot Power Query, open je een lege Excel-werkmap en ga je naar het tabblad Gegevens op het lint. Zoek naar de groep Gegevens ophalen en transformeren uiterst links.

Vanaf hier kun je op Gegevens ophalen klikken om een vervolgkeuzemenu met beschikbare gegevensbronnen te zien. Zodra je een bestand selecteert en op "Gegevens transformeren" klikt, opent Excel de Power Query-editor in een nieuw venster. Deze interface bestaat uit vier hoofdgebieden:

1. Het lint: Bovenaan gelegen, met tabbladen zoals Start, Transformeren, Kolom toevoegen en Beeld. Hier bevinden zich al je tools.
2. Het deelvenster Query's: Aan de linkerkant, met een lijst van alle actieve gegevensverbindingen in je werkmap.
3. Het voorbeeld van de gegevens: Het raster in het midden toont een live voorbeeld van hoe je gegevens eruitzien bij de momenteel geselecteerde stap.
4. Het deelvenster Query-instellingen (Toegepaste stappen): Aan de rechterkant. Dit is het belangrijkste deel van de editor. Telkens wanneer je iets wijzigt, slaat Power Query dit hier op als een opeenvolgende stap. Je kunt deze stappen op elk moment verwijderen, opnieuw ordenen of bewerken.

Stap-voor-stap handleiding: Rommelige verkoopgegevens opschonen

Laten we naar een praktisch voorbeeld uit de praktijk kijken. Stel je voor dat je wekelijks een verkooprapport exporteert uit het CRM van je bedrijf. De ruwe export is rommelig en bevat onnodige kopteksten, gecombineerde tekstreeksen en inconsistente opmaak.

Hier is een voorbeeld van onze ruwe, rommelige gegevens:

Systeemexport: Q3 Verkooprapport	Kolom2	Kolom3
Gegenereerd op: 10/01/2023
Rep_ID_Name	Order_Date	Revenue
101-John Doe	2023-08-15	1500.5
102-Jane Smith	09/01/2023	$2,340.00
103-Bob_Jones	23-Sep-2023	850.75

Als we traditionele formules zouden gebruiken, zouden we LEFT, RIGHT, FIND en VALUE moeten gebruiken om de namen van de vertegenwoordigers te extraheren en de getallen te corrigeren. Laten we in plaats daarvan Power Query gebruiken.

Stap 1: De gegevens importeren

Sla de rommelige gegevens op als een CSV- of Excel-bestand. Open een nieuwe Excel-werkmap, ga naar Gegevens > Gegevens ophalen > Uit bestand en selecteer je bestand. Wanneer het voorbeeldvenster verschijnt, klik je op Gegevens transformeren. De Power Query-editor wordt geopend.

Stap 2: Bovenste rijen verwijderen en veldnamen promoveren

De eerste twee rijen van onze gegevens zijn metadata van de systeemexport, geen daadwerkelijke gegevensrecords. We moeten ze verwijderen.

• Klik op het tabblad Start op Rijen verwijderen > Bovenste rijen verwijderen. Voer "2" in en klik op OK.
• Nu staan onze daadwerkelijke kolomkoppen in rij 1. Klik op de knop Eerste rij als veldnamen gebruiken op het tabblad Start. Direct worden "Rep_ID_Name", "Order_Date" en "Revenue" je officiële kolomtittels.

Stap 3: De kolom met vertegenwoordigers splitsen

De kolom "Rep_ID_Name" bevat zowel het ID-nummer als de naam van de werknemer, gescheiden door een koppelteken.

• Klik met de rechtermuisknop op de kolomkop "Rep_ID_Name".
• Selecteer Kolom splitsen > Op scheidingsteken.
• Kies "Aangepast" en typ een koppelteken ( - ). Klik op OK.
• Power Query splitst de gegevens in twee kolommen. Je kunt nu dubbelklikken op de nieuwe kolomkoppen om ze te hernoemen naar "Rep_ID" en "Rep_Name".

Om de onderstrepingstekens in de naam van Bob (Bob_Jones) op te schonen, klik je met de rechtermuisknop op de kolom Rep_Name, kies je Waarden vervangen, typ je een onderstrepingsteken (_) in het vak "Te zoeken waarde" en laat je "Vervangen door" leeg of voeg je een spatie toe. Klik op OK.

Stap 4: Gegevenstypen standaardiseren

Valt het je op hoe onze datums en inkomsten in totaal verschillende formaten staan? Power Query maakt het standaardiseren hiervan eenvoudig.

• Klik op het kleine pictogram aan de linkerkant van de kolomkop "Order_Date" (er staat waarschijnlijk "ABC" of "Willekeurig"). Verander dit naar Datum. Power Query vertaalt automatisch de verschillende datumformaten naar één gestandaardiseerde datum.
• Klik op het pictogram naast "Revenue" en verander dit naar Valuta (of Decimaal getal). Het verwijdert de dollartekens en maakt alles netjes op.

Stap 5: Een voorwaardelijke kolom toevoegen

Stel dat we verkopen van meer dan $1.000 willen categoriseren als "High Value". In plaats van een complexe IF-functie te schrijven zoals =IF(C2>=1000, "High Value", "Standard") in Excel, kunnen we de Power Query-gebruikersinterface gebruiken.

Ga naar het tabblad Kolom toevoegen en klik op Voorwaardelijke kolom. Stel de regels in: Als [Revenue] groter is dan of gelijk is aan 1000, voer dan "High Value" uit, anders "Standard". Achter de schermen genereert Power Query de volgende M-code voor deze stap:

= Table.AddColumn(#"Changed Type", "Sales Category", each if [Revenue] >= 1000 then "High Value" else "Standard")

Query's samenvoegen: Het moderne alternatief voor VLOOKUP

Een van de meest voorkomende taken bij gegevensanalyse is het combineren van tabellen. Als je een aparte tabel hebt met de regio voor elke verkoopvertegenwoordiger, zou je normaal gesproken onze complete gids voor VLOOKUP raadplegen om die gegevens binnen te halen.

Echter, het uitvoeren van duizenden VLOOKUP- of INDEX- en MATCH-formules kan je werkmap drastisch vertragen. In Power Query gebruik je de functie Query's samenvoegen.

Importeer simpelweg beide tabellen in Power Query, selecteer je belangrijkste verkooptabel en klik op Query's samenvoegen op het tabblad Start. Selecteer de tweede tabel (de Regio-tabel), klik op de overeenkomende kolom in beide tabellen (bijv. "Rep_ID") en klik op OK. Power Query voert in enkele seconden het equivalent uit van een razendsnelle VLOOKUP, ongeacht of je tien of tien miljoen rijen hebt.

Gegevens draaien opheffen (Unpivot): Het geheime wapen van Power Query

Vaak ontvang je gegevens die al in een draaitabel-achtige structuur zijn gegroepeerd (bijvoorbeeld maanden die over de kolommen lopen: Jan, Feb, Mrt, Apr). Hoewel dit voor mensen gemakkelijk te lezen is, is het verschrikkelijk voor het maken van grafieken of Draaitabellen.

Selecteer je identificatiekolommen (zoals Rep_Name), klik met de rechtermuisknop op de koptekst en kies Draaien van andere kolommen opheffen. Power Query transformeert je brede, kruistabelachtige gegevens direct naar een platte, tabelvormige lay-out met een nieuwe "Kenmerk" (Maand) en "Waarde" (Verkoop) kolom. Dit doen met standaard Excel-formules is bijna onmogelijk, wat Unpivot tot een van de meest geprezen functies van Power Query maakt.

De getransformeerde gegevens laden

Zodra je gegevens perfect schoon zijn, is het tijd om ze terug naar Excel te sturen.

Klik op het tabblad Start op Sluiten en laden. Standaard worden je getransformeerde gegevens geladen in een gloednieuwe, groene Excel-tabel op een nieuw werkblad. Als je de gegevens liever direct naar je analysefase stuurt, kun je op de vervolgkeuzepijl klikken, Sluiten en laden naar... kiezen en in plaats daarvan een Draaitabelrapport selecteren. Als je een opfriscursus nodig hebt over het bouwen van deze samenvattingen, bekijk dan onze tutorial over het maken van draaitabellen voor beginners.

De magie van de knop "Vernieuwen"

De ware kracht van Power Query wordt volgende week pas echt duidelijk, wanneer je een nieuwe ruwe verkoop export ontvangt. Herhaal de bovenstaande stappen niet!

Sla simpelweg het nieuwe CSV-bestand op over het oude (behoud exact dezelfde bestandsnaam en maplocatie). Open vervolgens je Excel-werkmap, klik met de rechtermuisknop ergens in je schone gegevenstabel en klik op Vernieuwen.

Power Query maakt verbinding met het bestand, past elke afzonderlijke stap opnieuw toe—rijen verwijderen, veldnamen promoveren, kolommen splitsen, tekst vervangen, voorwaarden controleren en tabellen samenvoegen—en werkt je definitieve uitvoer in een fractie van een seconde bij. Dit is een essentieel onderdeel van Excel-automatisering workflows.

Je datareis vereenvoudigen met AI

Hoewel Power Query structurele transformaties uitstekend afhandelt, heb je soms specifieke voorwaardelijke logica of complexe tekstontleding nodig die geavanceerde Excel-formules of aangepaste M-code vereisen. In plaats van forums af te speuren naar antwoorden, kun je kunstmatige intelligentie inzetten.

Als je worstelt met het schrijven van de perfecte berekening voor een aangepaste kolom, is ExcelGPT de ideale metgezel. Beschrijf gewoon in gewone taal wat je probeert te bereiken—bijvoorbeeld: "Ik heb een formule nodig om alleen de getallen uit een gemengde tekstreeks te extraheren"—en ExcelGPT genereert direct de juiste formule of M-code. Het combineren van Power Query met AI voor het opschonen van gegevens geeft je een onverslaanbare toolkit voor gegevensanalyse.

Veelgestelde vragen

Wijzigt Power Query mijn originele gegevensbestand?

Nee. Power Query maakt een eenrichtingsverbinding met je brongegevens. Het leest de gegevens, past de transformaties in het geheugen toe en voert een nieuw resultaat uit in Excel. Je originele CSV, database of werkmap blijft volledig onaangetast en veilig.

Kan ik Power Query op een Mac gebruiken?

Ja, Microsoft heeft de ondersteuning voor Power Query in Excel voor Mac aanzienlijk verbeterd. Hoewel de Mac-versie van oudsher enkele van de geavanceerde connectors en UI-functies miste die beschikbaar zijn op Windows, kun je nu in moderne versies van Microsoft 365 soepel verbinding maken met lokale bestanden en databases, en bestaande query's vernieuwen.

Wat is het verschil tussen Samenvoegen en Toevoegen in Power Query?

Samenvoegen (Merge) is het equivalent van een VLOOKUP of INDEX/MATCH. Je gebruikt het om nieuwe kolommen met gegevens toe te voegen door een gedeeld ID tussen twee tabellen te matchen. Toevoegen (Append) is als het kopiëren en plakken van gegevens onderaan een blad. Je gebruikt het om tabellen op elkaar te stapelen en nieuwe rijen toe te voegen (bijv. het combineren van verkopen in januari en verkopen in februari).

Waarom is het vernieuwen van mijn Power Query mislukt?

De meest voorkomende reden dat het vernieuwen van een query mislukt, is dat het bronbestand is verplaatst, hernoemd of verwijderd. Een ander veelvoorkomend probleem is dat een kolomkop in de ruwe gegevens is gewijzigd (bijv. "Revenue" werd door het systeem gewijzigd in "Total Revenue"). Je kunt dit oplossen door de Power Query-editor te openen, naar het deelvenster Toegepaste stappen te gaan en de stap Bron bij te werken, of de kolom in je staplogica te hernoemen.