Excel Power Query in 2026: ETL voor Data-analisten en Sollicitatievragen

Excel Power Query transformeert de manier waarop data-analisten ETL-workflows (Extract, Transform, Load) rechtstreeks in Excel uitvoeren. In tegenstelling tot handmatig kopiëren of complexe VBA-macro's biedt Power Query een visuele interface ondersteund door de M-taal, waardoor herhaalbare datatransformaties mogelijk zijn die met één klik worden bijgewerkt.

Welke problemen Power Query oplost voor data-analisten

Data-analisten besteden aanzienlijke tijd aan datavoorbereiding: bestanden uit verschillende bronnen samenvoegen, inconsistente formaten opschonen, irrelevante rijen filteren en tabellen herstructureren voor analyse. Power Query pakt deze taken aan via een query-editor die elke transformatiestap vastlegt. Wanneer de brongegevens wijzigen, wordt de volledige pipeline automatisch opnieuw uitgevoerd.

De workflow volgt drie fasen: verbinding maken met gegevensbronnen, transformaties toepassen en resultaten laden in Excel-tabellen of het gegevensmodel. Elke stap wordt vastgelegd in een formulebalk met M-taalsyntax, die direct kan worden bewerkt voor geavanceerde scenario's.

Deze aanpak verschilt van traditionele Excel-formules. Terwijl formules cellen herberekenen, werkt Power Query met hele tabellen voordat ze het werkblad bereiken. Een query die 50 CSV-bestanden consolideert, duplicaten verwijdert en kolommen ontpivoteert, wordt eenmaal uitgevoerd en produceert een schone tabel, in plaats van complexe geneste formules te bouwen die de werkmap vertragen.

Verbinding maken met gegevensbronnen via Gegevens ophalen

Power Query ondersteunt verbindingen met bestanden (CSV, Excel, JSON, XML), databases (SQL Server, MySQL, PostgreSQL, Oracle), cloudservices (SharePoint, Azure, Salesforce) en webpagina's. Het verbindingstype bepaalt welke authenticatie- en importopties verschijnen.

// Veelvoorkomende gegevensbronnen in Power Query
Gegevens > Gegevens ophalen > Uit bestand > Uit CSV
Gegevens > Gegevens ophalen > Uit database > Uit SQL Server-database
Gegevens > Gegevens ophalen > Uit andere bronnen > Van het web
Gegevens > Gegevens ophalen > Uit map (meerdere bestanden)

De optie "Uit map" is bijzonder nuttig voor het consolideren van meerdere bestanden. In plaats van elk bestand afzonderlijk te importeren, scant Power Query een map, lijst alle overeenkomende bestanden op en combineert ze in één query. Een nieuw bestand toevoegen aan de map neemt dit automatisch mee bij de volgende vernieuwing.

Bij verbinding met een SQL-database kan Power Query transformatielogica naar de server doorschuiven via query folding. Filters en kolomselecties worden vertaald naar SQL WHERE- en SELECT-clausules, waardoor de hoeveelheid data die naar Excel wordt overgebracht afneemt. De formulebalk toont een optie "Native query weergeven" wanneer folding actief is.

Kerntransformaties in de Query-editor

De Query-editor biedt lintopdrachten voor veelvoorkomende bewerkingen, maar het begrijpen van de onderliggende M-code helpt wanneer aanpassingen nodig zijn. Elke transformatie voegt een stap toe aan het deelvenster "Toegepaste stappen", waardoor een controleerbare reeks ontstaat.

Rijen filteren en sorteren

Filteren verwijdert rijen die niet aan de criteria voldoen. Het kolomkop-dropdown biedt snelle filters, terwijl het dialoogvenster "Rijen filteren" complexe voorwaarden met AND/OR-logica ondersteunt.

// FilteredRows-stap in M-taal
let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
    FilteredRows = Table.SelectRows(Source, each [Region] = "EMEA" and [Amount] > 1000)
in
    FilteredRows

De functie Table.SelectRows neemt een tabel en een voorwaarde. Het sleutelwoord each creëert een functie waarbij _ de huidige rij vertegenwoordigt, en veldtoegang gebruikt haakjesnotatie [Region]. Meerdere voorwaarden worden gecombineerd met and of or operatoren.

Kolommen verwijderen en hernoemen

Gegevensbronnen bevatten vaak kolommen die niet nodig zijn voor analyse. Vroeg verwijderen vermindert geheugengebruik en vereenvoudigt volgende stappen.

// Kolommen verwijderen, dan resterende hernoemen
let
    Source = Excel.CurrentWorkbook(){[Name="RawData"]}[Content],
    RemovedColumns = Table.RemoveColumns(Source, {"TempID", "InternalNotes", "Debug"}),
    RenamedColumns = Table.RenameColumns(RemovedColumns, {{"Cust_Name", "CustomerName"}, {"Amt", "Amount"}})
in
    RenamedColumns

De kolomlijst gebruikt accolades {} voor meerdere items. Hernoemen vereist een lijst van paren, waarbij elk paar de oude naam en nieuwe naam bevat. Consistente naamgevingsconventies over query's heen vergemakkelijken het combineren van datasets.

Kolommen splitsen en samenvoegen

Tekstkolommen vereisen vaak parsing. Een "FullName"-kolom moet mogelijk worden gesplitst in voor- en achternaam, of afzonderlijke datum- en tijdkolommen moeten worden samengevoegd.

// FullName splitsen op scheidingsteken in twee kolommen
let
    Source = Excel.CurrentWorkbook(){[Name="Contacts"]}[Content],
    SplitColumn = Table.SplitColumn(Source, "FullName", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"FirstName", "LastName"})
in
    SplitColumn

De functie Splitter.SplitTextByDelimiter verzorgt de parsing-logica. Voor complexere patronen bieden Splitter.SplitTextByEachDelimiter of Splitter.SplitTextByPositions extra controle. Wanneer het aantal resulterende kolommen varieert, maakt Power Query kolommen dynamisch aan.

Typeconversies en datakwaliteit

Power Query leidt kolomtypes af bij import, maar expliciete typetoewijzing detecteert fouten vroegtijdig. Een tekstkolom met numerieke ID's moet tekst blijven als voorloopnullen belangrijk zijn. Datumkolommen geïmporteerd als tekst veroorzaken sorteerproblemen.

// Expliciete typetoewijzingen
let
    Source = Csv.Document(File.Contents("C:\\Data\\transactions.csv")),
    TypedColumns = Table.TransformColumnTypes(Source, {
        {"TransactionID", type text},
        {"Date", type date},
        {"Amount", type number},
        {"IsProcessed", type logical}
    })
in
    TypedColumns

Het sleutelwoord type specificeert het doeltype. Beschikbare types zijn onder meer text, number, date, datetime, datetimezone, time, duration, logical en binary. Typefouten verschijnen als "Error"-waarden in cellen, waardoor datakwaliteitsproblemen zichtbaar worden vóór analyse.

Het afhandelen van null-waarden vereist expliciete logica. De functie Table.ReplaceValue vervangt nulls door standaardwaarden, terwijl Table.SelectRows met [Column] <> null ze uitfiltert.

Groeperen en aggregatie met Groeperen op

Data aggregeren per categorie is een veelvoorkomende vereiste. De "Groeperen op"-transformatie comprimeert rijen met dezelfde sleutelwaarden en past aggregatiefuncties toe.

// Verkopen groeperen per regio en jaar, som en telling berekenen
let
    Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
    Grouped = Table.Group(Source, {"Region", "Year"}, {
        {"TotalSales", each List.Sum([Amount]), type number},
        {"OrderCount", each Table.RowCount(_), Int64.Type},
        {"AvgOrderValue", each List.Average([Amount]), type number}
    })
in
    Grouped

De groeperingskolommen verschijnen eerst, gevolgd door aggregatiedefinities. Elke aggregatie specificeert een nieuwe kolomnaam, een aggregatiefunctie en een optioneel resultaattype. Het sleutelwoord each vertegenwoordigt de subtabel voor elke groep, waardoor elke tabel- of lijstfunctie mogelijk is.

Geneste aggregaties maken berekeningen mogelijk zoals "percentage van groepstotaal" door zowel de rijwaarde als het groepaggregaat te refereren in een volgende stap.

Pivoteren en ontpivoteren voor dataherstructurering

Pivoteren converteert rijwaarden naar kolommen en creëert een kruistabellay-out. Ontpivoteren doet het omgekeerde en converteert kolommen naar rijen voor genormaliseerde structuren.

// Maandkolommen ontpivoteren naar rijen
let
    Source = Excel.CurrentWorkbook(){[Name="MonthlySales"]}[Content],
    // Origineel: Product, Jan, Feb, Mar, Apr kolommen
    Unpivoted = Table.UnpivotOtherColumns(Source, {"Product"}, "Month", "Sales")
    // Resultaat: Product, Month, Sales kolommen
in
    Unpivoted

De functie Table.UnpivotOtherColumns houdt gespecificeerde kolommen vast en ontpivoteert de rest. Dit is veiliger dan alle te ontpivoteren kolommen op te sommen, omdat nieuwe maandkolommen automatisch worden opgenomen. De twee laatste parameters benoemen de attribuutkolom ("Month") en waardekolom ("Sales").

Pivoteren gebruikt Table.Pivot met een aggregatiefunctie voor gevallen waar meerdere waarden bestaan voor dezelfde rij-kolomcombinatie:

// Verkopen pivoteren per regio
let
    Source = Excel.CurrentWorkbook(){[Name="DetailedSales"]}[Content],
    Pivoted = Table.Pivot(Source, List.Distinct(Source[Region]), "Region", "Amount", List.Sum)
in
    Pivoted

Query's samenvoegen en toevoegen

Data uit meerdere bronnen combineren is waar Power Query handmatige inspanning vermindert. Samenvoegen voert een join uit tussen twee tabellen op basis van overeenkomende kolommen. Toevoegen stapelt tabellen verticaal.

// Left Join: Orders met Klantgegevens
let
    Orders = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
    Customers = Excel.CurrentWorkbook(){[Name="Customers"]}[Content],
    Merged = Table.NestedJoin(Orders, {"CustomerID"}, Customers, {"ID"}, "CustomerDetails", JoinKind.LeftOuter),
    Expanded = Table.ExpandTableColumn(Merged, "CustomerDetails", {"Name", "Email"})
in
    Expanded

De functie Table.NestedJoin creëert een geneste tabelkolom met overeenkomende rijen. De functie Table.ExpandTableColumn maakt vervolgens de geneste structuur plat tot reguliere kolommen. Join-types omvatten Inner, LeftOuter, RightOuter, FullOuter, LeftAnti en RightAnti.

Toevoegen met Table.Combine vereist overeenkomende kolomnamen. Wanneer schema's verschillen, zorgt Table.SelectColumns op elke bron vóór combineren voor consistentie:

// Twee verkooptabellen met consistente kolommen toevoegen
let
    Sales2024 = Table.SelectColumns(Source2024, {"Date", "Product", "Amount"}),
    Sales2025 = Table.SelectColumns(Source2025, {"Date", "Product", "Amount"}),
    Combined = Table.Combine({Sales2024, Sales2025})
in
    Combined

Aangepaste kolommen en voorwaardelijke logica

De functie "Kolom toevoegen > Aangepaste kolom" maakt berekende velden mogelijk met M-expressies. Voorwaardelijke logica gebruikt if-then-else-syntax.

// Een berekende kolom met voorwaardelijke logica toevoegen
let
    Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
    AddedColumn = Table.AddColumn(Source, "OrderCategory", each 
        if [Amount] >= 10000 then "Enterprise"
        else if [Amount] >= 1000 then "Business"
        else "Consumer",
        type text
    )
in
    AddedColumn

De if-expressie moet zowel then- als else-takken bevatten. Geneste voorwaarden schakelen met else if. De laatste parameter specificeert het kolomtype, wat de prestaties verbetert en type-inferentieproblemen voorkomt.

Voor complexe transformaties houden helperfuncties gedefinieerd in het let-blok de code leesbaar:

let
    // Helperfunctie voor fiscaal kwartaal
    GetFiscalQuarter = (date as date) as text =>
        let
            month = Date.Month(date),
            fiscalQ = if month >= 4 and month <= 6 then "Q1"
                     else if month >= 7 and month <= 9 then "Q2"
                     else if month >= 10 and month <= 12 then "Q3"
                     else "Q4"
        in
            fiscalQ,
    
    Source = Excel.CurrentWorkbook(){[Name="Transactions"]}[Content],
    AddedQuarter = Table.AddColumn(Source, "FiscalQuarter", each GetFiscalQuarter([Date]), type text)
in
    AddedQuarter

Foutafhandeling in Power Query

Transformatiefouten verschijnen als "Error"-waarden in cellen in plaats van de hele query te laten mislukken. Het try-otherwise-construct handelt fouten elegant af:

// Potentiële delingsfouten afhandelen
let
    Source = Excel.CurrentWorkbook(){[Name="Metrics"]}[Content],
    AddedRatio = Table.AddColumn(Source, "Ratio", each 
        try [Value1] / [Value2] otherwise null,
        type number
    )
in
    AddedRatio

Het sleutelwoord try probeert de expressie en retourneert een record met HasError- en Value-velden. De otherwise-clausule biedt een fallback wanneer HasError true is. Voor meer controle, toegang tot de foutdetails met try Expression:

// Foutdetails vastleggen
let
    result = try SomeRiskyFunction(),
    output = if result[HasError] then "Error: " & result[Error][Message] else result[Value]
in
    output

Power Query sollicitatievragen voor data-analisten

Interviewers beoordelen zowel praktische vaardigheden als begrip van wanneer Power Query in een workflow past. Deze vragen komen vaak voor in sollicitatiegesprekken voor data-analisten.

Wat is query folding en waarom is het belangrijk?

Query folding vertaalt Power Query-transformaties naar native queries voor de gegevensbron. Bij verbinding met SQL Server wordt een filterstap een WHERE-clausule die op de server wordt uitgevoerd, waardoor de netwerkoverdracht afneemt. Niet alle transformaties worden gefolded: aangepaste M-functies, bepaalde datummanipulaties en bewerkingen na een niet-foldende stap onderbreken de keten. Controleer de foldingstatus door met de rechtermuisknop op een stap te klikken en te zoeken naar "Native query weergeven".

Hoe verschilt Power Query van Excel-formules voor datatransformatie?

Excel-formules werken cel voor cel binnen het werkblad en herberekenen bij elke wijziging. Power Query werkt met tabellen voordat ze het werkblad bereiken en verwerkt data in bulk tijdens vernieuwen. Voor transformaties zoals ontpivoteren, deduplicatie of het samenvoegen van bestanden drukt Power Query de logica directer uit dan geneste INDEX-MATCH of hulpkolommen.

Wanneer zou Power Query boven Power BI worden gekozen voor ETL?

Power Query in Excel is geschikt voor scenario's waarin analisten getransformeerde data in spreadsheetvorm nodig hebben voor ad-hoc analyse, draaitabellen of delen met gebruikers die geen Power BI-toegang hebben. Power BI biedt rijkere visualisatie, grotere datacapaciteit en enterprise deelfuncties. Dezelfde M-code werkt in beide tools, dus queries ontwikkeld in Excel kunnen zonder herschrijven naar Power BI Desktop migreren.

Hoe worden datatypeconflicten afgehandeld bij het toevoegen van tabellen?

Stel expliciete types in op elke brontabel vóór combineren met Table.Combine. Als een kolom tekst is in één bron en nummer in een andere, mislukt het toevoegen of produceert fouten. Gebruik Table.TransformColumnTypes op beide bronnen om consistente types af te dwingen. Het try-otherwise-patroon handelt randgevallen af waar conversie mislukt voor specifieke waarden.

Leg het verschil uit tussen Samenvoegen en Toevoegen in Power Query.

Samenvoegen voert een horizontale join uit op basis van overeenkomende sleutelkolommen, vergelijkbaar met SQL JOIN. Toevoegen voert een verticale unie van rijen uit meerdere tabellen uit, vergelijkbaar met SQL UNION ALL. Samenvoegen vereist ten minste één gemeenschappelijke kolom voor matching. Toevoegen vereist kolommen met dezelfde namen om correct uit te lijnen.

Voor diepere voorbereiding op SQL-concepten die Power Query-vaardigheden aanvullen, behandelt de SQL window functions module ranking- en aggregatiepatronen, terwijl de SQL subqueries en CTEs module querystructureringstechnieken behandelt.

Laadopties en vernieuwingsstrategieën

Na transformaties biedt de knop "Sluiten & laden" laadkeuzes: laden naar een werkbladtabel, alleen laden naar het gegevensmodel (Power Pivot) of een alleen-verbinding query maken. Alleen-verbinding queries dienen als staging-stappen voor andere queries zonder werkbladruimte te gebruiken.

Vernieuwingsgedrag hangt af van de laadbestemming. Werkbladtabellen vernieuwen met de knop "Alles vernieuwen" of kunnen worden ingesteld om bij het openen van het bestand te vernieuwen. Gegevensmodeltabellen nemen deel aan de vernieuwingscyclus van het gegevensmodel van de werkmap. Voor queries verbonden met externe databases kunnen referentieprompts verschijnen bij vernieuwen tenzij opgeslagen referenties bestaan.

Achtergrondvernieuwing laat de werkmap bruikbaar blijven tijdens het laden van data, maar creëert complexiteit wanneer downstream formules afhankelijk zijn van vernieuwde data. De optie "Achtergrondvernieuwing inschakelen" in query-eigenschappen beheert dit gedrag. Voor kritieke rapporten zorgt het uitschakelen van achtergrondvernieuwing voor sequentiële uitvoering.

Prestatieoverwegingen voor grote datasets

Power Query kan miljoenen rijen verwerken, maar de responsiviteit van de werkmap hangt af van hoe data wordt geladen. Laden naar het gegevensmodel in plaats van werkbladtabellen vermijdt Excel's rijlimiet en verbetert draaitabelprestaties. Onnodige kolommen vroeg verwijderen vermindert de geheugenvoetafdruk.

Voor queries die enkele minuten duren, toont de "Datapreview" in de Query-editor slechts een sample. Transformaties worden toegepast op de volledige dataset tijdens vernieuwen. Fouten zichtbaar in preview indiceren problemen, maar sommige fouten verschijnen alleen met volledige data. Een testvernieuwing op een subset valideert de query voordat alles wordt verwerkt.

Bij het consolideren van veel bestanden verwerkt Power Query's binaire combinatiefunctie bestanden parallel. Een functie definiëren die één bestand transformeert en deze vervolgens aanroepen voor elke rij in de bestandslijst biedt meer controle dan de standaard combinatieaanpak.

De Microsoft-documentatie over Power Query best practices beschrijft optimalisatietechnieken inclusief queryafhankelijkheidsstructuren en buffergebruik.

Power Query voor herhaalbare ETL-pipelines

De vastgelegde stappen in Power Query creëren documentatie van de transformatielogica. Wanneer vereisten wijzigen, updatet het aanpassen van een stap automatisch alle downstream stappen. Dit contrasteert met ad-hoc Excel-manipulaties die opnieuw moeten worden gecreëerd wanneer brondata wijzigen.

Teams kunnen queries delen door verbindingen te exporteren of query M-code op te slaan in versiebeheer. De Geavanceerde Editor (Weergave > Geavanceerde Editor) toont het volledige M-script, dat kan worden gekopieerd en geplakt in een andere werkmap. Voor enterprise scenario's bieden Power BI dataflows en Fabric gecentraliseerd querybeheer.

• Power Query handelt ETL binnen Excel af met vastgelegde, herhaalbare transformatiestappen
• De M-taal onder de visuele interface maakt aanpassingen mogelijk buiten lintopdrachten
• Query folding duwt filters en projecties naar brondatabases voor betere prestaties
• Samenvoegen voert joins uit tussen tabellen, Toevoegen stapelt tabellen verticaal
• Typetoewijzingen en foutafhandeling voorkomen stille datakwaliteitsproblemen
• Alleen-verbinding queries creëren herbruikbare staging-stappen zonder werkbladuitvoer
• Sollicitatievragen focussen op query folding, formulevergelijking en samenvoegen versus toevoegen-onderscheidingen