Dit artikel is gebaseerd op de Nederlandse versie van Excel.
Power Query is het onderdeel van Excel waarmee je gegevens ophaalt, opschoont en combineert, zonder formules en zonder steeds hetzelfde handwerk. Je vindt het op het tabblad Gegevens, in de groep Gegevens ophalen en transformeren. Power Query onthoudt elke stap die je zet, zodat je een volgende keer alleen op Alles vernieuwen klikt om de nieuwe gegevens op dezelfde manier te verwerken.
Wat is Power Query?
Veel mensen besteden elke week of maand tijd aan dezelfde klus: een export uit een systeem openen, lege regels verwijderen, kolommen splitsen, datums goedzetten en bestanden aan elkaar plakken. Power Query automatiseert precies dat. Je voert de stappen een keer uit in de Power Query-editor, en Excel legt ze vast als een recept. Komt er een nieuwe export, dan past Power Query hetzelfde recept toe.
Power Query zit standaard in Excel voor Windows (Microsoft 365, Excel 2016 en nieuwer) en in Power BI Desktop. In Excel voor Mac is het beschikbaar in Microsoft 365, met iets minder bronnen.
Waarvoor gebruik je Power Query?
- Exports opschonen: lege rijen en overbodige kolommen weg, tekst splitsen, datums en getallen goed zetten.
- Bestanden combineren: alle maandbestanden uit een map samenvoegen tot een tabel.
- Tabellen koppelen: twee lijsten samenvoegen op een gemeenschappelijke kolom, zoals een klantnummer. Dit vervangt veel VERT.ZOEKEN-formules.
- Draaien van gegevens: een overzicht met maanden als kolommen omzetten naar een lange lijst (en terug), zodat je er een draaitabel van kunt maken.
- Gegevens ophalen: uit CSV-bestanden, andere Excel-bestanden, SharePoint, databases of webpagina's.
Je eerste query in 5 stappen
Stel je krijgt elke maand een CSV-export met inschrijvingen. Er staan lege regels in, de naam staat in een kolom als "Achternaam, Voornaam" en de datum wordt als tekst ingelezen.
- Gegevens ophalen: ga naar Gegevens en kies Uit tekst/CSV. Selecteer het bestand en klik op Gegevens transformeren. De Power Query-editor opent.
- Lege rijen verwijderen: kies op het tabblad Start voor Rijen verwijderen en dan Lege rijen verwijderen.
- Kolom splitsen: selecteer de naamkolom, kies Kolom splitsen en dan Op scheidingsteken. Kies de komma.
- Gegevenstype instellen: klik op het pictogram links in de kop van de datumkolom en kies Datum. Doe hetzelfde voor bedragen met Decimaal getal.
- Laden: kies Sluiten en laden. Het resultaat verschijnt als tabel op een nieuw werkblad.
Rechts in de editor, onder Toegepaste stappen, zie je elke stap terug. Klik je op een stap, dan zie je hoe de gegevens er op dat moment uitzagen. Een stap verwijderen of aanpassen kan altijd.
Volgende maand: een klik
Komt er een nieuwe export, vervang dan het oude bestand door het nieuwe (met dezelfde naam en op dezelfde plek). Kies daarna Gegevens en Alles vernieuwen. Power Query voert alle stappen opnieuw uit. Wat eerst een kwartier handwerk was, kost nu een paar seconden.
Alle bestanden uit een map combineren
Heb je per maand een apart bestand met dezelfde kolommen? Kies Gegevens, Gegevens ophalen, Uit bestand en dan Uit map. Selecteer de map en kies Combineren en transformeren. Power Query plakt alle bestanden onder elkaar en voegt een kolom toe met de bestandsnaam, zodat je weet uit welke maand een regel komt. Zet je volgende maand een nieuw bestand in de map, dan neemt Alles vernieuwen het automatisch mee.
Twee tabellen samenvoegen
Met Query's samenvoegen (tabblad Start in de editor) koppel je twee tabellen op een gemeenschappelijke kolom, bijvoorbeeld een lijst met inschrijvingen en een lijst met cursusprijzen op cursuscode. Je kiest welke kolommen je uit de tweede tabel wilt toevoegen. Dit doet hetzelfde als een reeks VERT.ZOEKEN-formules, maar blijft werken als de lijsten langer worden.
Power Query, formules of een draaitabel?
- Power Query gebruik je om gegevens binnen te halen en klaar te zetten.
- Formules gebruik je voor berekeningen die direct mee moeten veranderen als je iets invult.
- Een draaitabel gebruik je om de opgeschoonde gegevens samen te vatten. Power Query en een draaitabel vormen samen een sterke combinatie.
Power Query in Power BI
Power BI Desktop gebruikt dezelfde Power Query-editor. Wat je in Excel leert, kun je dus direct toepassen in Power BI. Lees meer in wat is Power BI.
Veelvoorkomende problemen
- Fout bij vernieuwen: bestand niet gevonden: het bronbestand is verplaatst of hernoemd. Pas de bron aan via Gegevens, Query's en verbindingen, rechtermuisknop op de query en Bewerken, dan de eerste stap Bron.
- Datums of bedragen kloppen niet: de landinstelling van de bron wijkt af. Stel het type in met Type wijzigen en dan Met landinstelling, en kies bijvoorbeeld Nederlands (Nederland).
- Kolom ontbreekt na vernieuwen: de export heeft een andere kolomnaam gekregen. Power Query zoekt kolommen op naam, dus pas de stap aan waarin de oude naam voorkomt.
Veelgestelde vragen
Is Power Query gratis?
Ja. Power Query zit standaard in Excel voor Windows vanaf versie 2016 en in Microsoft 365. Je hoeft niets te installeren.
Moet ik kunnen programmeren voor Power Query?
Nee. Bijna alles doe je met knoppen en menu's. Achter de schermen schrijft Power Query code in de taal M, maar die hoef je niet te kennen om er goed mee te werken.
Wat is het verschil tussen Power Query en Power Pivot?
Power Query haalt gegevens op en schoont ze op. Power Pivot bouwt daarna een datamodel met relaties tussen tabellen en berekeningen. Ze worden vaak samen gebruikt.
Waar vind ik Power Query in een oudere Excel?
In Excel 2010 en 2013 was Power Query een aparte invoegtoepassing. Vanaf Excel 2016 heet het Gegevens ophalen en transformeren en staat het op het tabblad Gegevens.
Leren werken met Power Query?
Power Query komt aan bod in de cursussen Excel Gevorderd en Excel analyse en rapportage. Wil je de volgende stap zetten naar interactieve dashboards, dan sluit de Power BI cursus er goed op aan. Kleine groepen, klassikaal op 17 locaties of live online.