Get & Transform with Power Query
A recordable, replayable cleaning pipeline inside Excel
Power Query is Excel's ETL engine: repeatable steps to import, clean, reshape and merge data, captured as queries that replay on every refresh.
What you will learn
- Build a query from a file/folder/table
- Apply transforms as recorded steps
- Merge and append queries into a clean data model
What is this?
Power Query (Get & Transform) is the built-in ETL tool that records every transformation as a step in a query (.pqx / M). You import a messy file, click your way through cleaning, merging and reshaping, and the query replays identically on refresh from the same source — a mini-pipeline that Excel users actually maintain.
Why do you need it?
- Excel-only analysis breaks when the export changes shape; Power Query absorbs the variance.
- It lifts the 1,048,576-row Excel limit for the pipeline stage by streaming from the source.
- Recorded steps provide auditability — reviewers can read exactly what happened to the data.
- Merging in Power Query replaces fragile VLOOKUP joins with a real join engine.
Mental model
A recipe card a sous-chef replays exactly
A Power Query
Write the steps once, and every kitchen (refresh) produces the same dish from whatever the market delivers this week. Improve the card once, and every future batch improves.
How it works
- Create a query from a source: file, folder, table or database with full schema inference.
- Apply steps: promote headers, change types, filter, split, trim, replace values, dedupe.
- Merge queries build proper joins (inner/left/anti) with a join key.
- Append queries stack same-shaped tables into one longer table.
- Load the result to a worksheet or the Data Model; refresh re-runs all steps from scratch.
A Power Query pipeline.
Examples
The steps you likely record
// Query 'Sales' (invisible click-for-click in the UI)Source = Excel.Workbook(...),Promoted = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),Typed = Table.TransformColumnTypes(Promoted, {{"Amount", type number}}),Filtered = Table.SelectRows(Typed, each [Amount] > 0),Trimmed = Table.TransformColumns(Filtered, {{"Region", Text.Trim}}),Deduped = Table.Distinct(Trimmed, {"order_id"})Every UI click maps to an M step like these. The step list is the pipeline: readable, debuggable and replayable.
In production
- A monthly business process: one workbook pulls three CSV exports from a folder, promotes headers, dedupes, merges on sku_id and loads a single pivot-ready table.
- A template workbook with a blank-query catalog that new team members parameterize for their own files.
- Merging regional files into one national view without copy-pasting a single row.
Best practices
- Name your queries and each important step; unnamed steps are unreadable in audits.
- Promote headers and set types early so later steps operate on the right schema.
- Keep source data read-only and never modify the original file from a query.
- Parameterize paths/dates so the query survives new folders and months.
Common mistakes
- Embedding file paths that break the moment a folder moves.
- Cleaning in Excel after loading instead of adding one more Power Query step.
- Using full-file loads plus merging in Excel — the query should own the join.
- Ignoring type changes warnings that silently coerce data.
Debugging tips
- Preview a step by selecting it in the Applied Steps pane; everything later is greyed out.
- When a refresh fails, hover the failing step — the error names the column and value.
- Compare source headers after a schema change; promoted-header steps fail loudly.
- Use the right-click 'Add Custom Column' with a small M expression to test logic on a sample.
Interview checkpoint
Hands-on lab
- Import a messy CSV and promote headers
- Record cleaning steps in the Applied Steps list
- Refresh a dirty new file through the same query
Build a Replayable Cleaning Query
25 min · intermediate
Quick quiz
What does 'Merge Queries' do in Power Query?
Power Query steps re-run automatically every time the workbook refreshes.