Power Query is the data-shaping step behind every Power BI model: click Transform Data on Desktop’s Home ribbon and it opens the Power Query Editor, the same M-language editor Excel’s Get & Transform Data uses. Every cleaning and reshaping action recorded there, merging, grouping, splitting, filtering, becomes one step of M code, and Close & Apply runs every query and loads the result into the report’s data model.
Below, every result shown, from join row counts to group-by totals, is copied from the already-verified Power Query quiz bank, not re-derived by hand.
In this guide
- Getting into the editor
- Merge Queries: the six join kinds
- Append Queries
- Group By
- Reshaping: unpivot, fill, split, index, custom columns
- Data types and errors
- Parameters
- Query folding
- Reference, Duplicate, Enable Load and dependencies
- Data profiling and combining files
- Common mistakes
- Cheat sheet
- Try it yourself
- FAQ
Getting into the editor
Transform Data, on Desktop’s Home ribbon, opens the Power Query Editor, where a table’s transformation steps are built and edited. Every query is really just M code under the hood, visible in full as a single let...in expression through View → Advanced Editor, where steps can be read, written or restructured by hand instead of only through the ribbon.
Close & Apply runs every query and loads the current changes into the report’s data model. Simply closing the editor window instead abandons whatever was changed in that editing session, leaving the report exactly as it was before.
Merge Queries: the six join kinds
Merging two queries works exactly like a SQL join: pick a matching column on each side and a join kind. Using one small example, Orders (three rows, one order per customer 101, 102 and 104) merged against Accounts (three rows, for customers 101, 102 and 103), every join kind gives a different row count, and the pattern is worth memorising cold for an interview.
| Join kind | Rows kept | What survives |
|---|---|---|
| Inner | 2 | Only rows with a match on both sides: customers 101 and 102 |
| Left Outer | 3 | Every row of the first table (Orders), matched or not; order 104 gets a blank account |
| Right Outer | 3 | Every row of the second table (Accounts), matched or not; account 103 gets no order |
| Full Outer | 4 | Everything from both sides: the two matches, plus the unmatched order and the unmatched account |
| Left Anti | 1 | Only rows of the first table with no match in the second: just order 104 |
| Right Anti | 1 | Only rows of the second table with no match in the first: just account 103 |
A merge can also match on more than one column at once, for example Yr and Reg together: a row then only counts as a match when both columns agree, which is stricter than matching on either column alone and typically keeps fewer rows than a single-column merge would.
let
Orders = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
Accounts = Excel.CurrentWorkbook(){[Name="Accounts"]}[Content],
Merged = Table.NestedJoin(Orders, {"CustID"}, Accounts, {"ID"}, "Acc", JoinKind.Inner)
in
Merged
Append Queries
Append stacks rows instead of matching columns side by side, and it matches columns purely by name. A table First with City and Units, appended to a table Second with City and Weight, produces one City column shared by both, plus a separate Units column and a separate Weight column, each filled with blank wherever the other table has no such column. Columns with the same name are matched, never duplicated.
Group By
Grouping by Zone over the revenue values N 120, S 80, N 45, S 200, N 60 gives two very different results depending on the chosen aggregation:
| Aggregation | N | S |
|---|---|---|
| Sum of Revenue | 225 (120 + 45 + 60) | 280 (80 + 200) |
| Count Rows | 3 | 2 |
Count Rows simply counts how many records fall into each group, completely independent of what the revenue values in those rows actually are, which is the mix-up this topic’s quiz catches most often.
Reshaping: unpivot, fill, split, index, custom columns
| Transform | What it does | Worked example |
|---|---|---|
| Unpivot Other Columns | Turns wide columns into row pairs of attribute and value | Product/Q1/Q2 becomes Product, Quarter, Value; the first row produced pairs Lamp with Q1 and 5, before Lamp/Q2, Mug/Q1, Mug/Q2 |
| Fill Down | Copies the last seen value downward into every blank below it | Fruit, blank, blank, Veg, blank becomes Fruit, Fruit, Fruit, Veg, Veg |
| Fill Up | Copies the next seen value upward into every blank above it | Blank, blank, Fruit, blank, Veg becomes Fruit, Fruit, Fruit, Veg, Veg |
| Split Column by delimiter | Cuts each value at the delimiter into separate columns, row by row | Asha|Pune and Ravi|Mumbai, split on “|”, give Asha, Pune, Ravi, Mumbai |
| Add Index Column | Adds a running number, with a chosen start and increment | Starting at 1, incrementing by 2, over three rows gives 1, 3, 5 |
| Add Custom Column | Adds a column computed from an M expression, such as nested if/then/else | Amount 40, 120, 75 tiered at 100 and 50 gives Low, High, Mid, in that row order |
| Transform Columns | Applies one function to every row of each listed column at once | Text.Upper on both City and Country turns pune/india into PUNE/INDIA, not just one of the two columns |
Fill Down and Fill Up are easy to mix up under pressure: Fill Down looks upward for the value to copy into a blank, Fill Up looks downward. Reading the command name backward (“fill this blank by looking down” for Fill Up) is a reliable way to keep the two straight.
Data types and errors
Changing a column’s type to Decimal Number when one value cannot convert, such as the text xyz sitting among numbers, turns only that one cell into an error; the rest convert normally. From there, two different commands handle the error very differently:
| Command | What happens to the error |
|---|---|
| Replace Errors | Keeps the row, substituting a chosen value, such as -1, into just the failed cell |
| Remove Errors | Deletes the whole row that contains the error, losing every other value in it too |
Inside an M expression itself, try [expression] otherwise [fallback] is M’s own try/catch: it evaluates the expression, and if that expression errors, returns the fallback value instead, catching the problem inline rather than cleaning it up after the fact.
Text comparisons in M are case sensitive. [Status] = "open" treats Open and open as two different values, unlike an ordinary Excel formula comparison, which ignores case. Wrap both sides in Text.Lower first if the comparison should ignore case.
Parameters
A query parameter is a named, reusable value, created once through Manage Parameters, that other queries reference instead of a value hard-coded into their own steps. A filter such as Table.SelectRows(SalesTbl, each [Amount] > MinAmount) uses a parameter exactly like it would use a literal number.
The most common real use is switching a whole set of queries between a Dev and a Prod data source: if every query’s source step reads from the same parameter rather than a literal path, flipping that one parameter’s value repoints the entire model in a single edit instead of editing every query by hand.
Query folding
Query folding is Power Query translating its steps into the source’s own language, typically SQL, so the source itself does the filtering, grouping and sorting instead of every raw row being pulled across first. For a large source, this is the single biggest lever on refresh speed.
Most steps fold cleanly: renaming a column, removing a column, or changing a type to match what the source already stores. A step with no real SQL equivalent, such as a custom row-by-row M calculation, breaks the chain: Power Query must pull the data across first and cannot fold any further from that point on. Right-clicking a step and seeing an active View Native Query option is the tell: it stays enabled only while folding is unbroken, letting the actual SQL be inspected, and it greys out the moment a step can no longer be folded.
Why this matters for performance
Filtering out unneeded rows as early as possible in a query’s steps gives that filter the best chance of folding back to the source, so the source returns fewer rows in the first place and every later step has noticeably less data to process.
Reference, Duplicate, Enable Load and dependencies
| Action | What it does |
|---|---|
| Reference | Builds a new query that starts from the first query's finished result, and stays linked: a later change to the original flows through |
| Duplicate | Copies every step into a new, fully independent query that can diverge freely from the original |
| Disable Enable Load | Keeps a query usable as a source for other queries in the same file, without it appearing as its own visible table in the report |
| Query Dependencies view | A diagram showing which queries feed into which others, including references and merges |
A common pattern uses all four together: a disabled-load staging query holds a shared cleanup step, several other queries reference it rather than duplicate its steps, and the Query Dependencies view keeps the resulting chain readable instead of scrolling through the query list guessing what feeds what.
Data profiling and combining files
Under the View tab, Column Quality, Column Distribution and Column Profile give a statistical summary of each column: the share of valid, error and empty values, how many distinct and unique values exist, and a detailed breakdown for one selected column.
Combine Files from a folder builds a template query from one sample file and re-applies the same transformation to every matching file found there, appending the results together, which is the standard way to handle a folder of monthly CSV exports: a new month’s file gets picked up automatically on the next refresh, with no query edit needed.
A data source’s privacy level, Public, Organizational or Private, is a data-safety control, not a performance setting: it governs whether Power Query is allowed to fold or combine data across different sources, to stop sensitive data from one source leaking into another through a combined query, which is why a mix of privacy levels sometimes forces a slower, separately evaluated refresh.
Common mistakes
- Assuming Left Outer and Right Outer are interchangeable. They are mirror images of each other, defined by which table is “first” in the merge, and swapping which table a join treats as first changes the result.
- Comparing text with the wrong case assumption. M’s equality operator is case sensitive, unlike an ordinary Excel formula comparison.
- Filtering late instead of early. A filter added after several non-folding steps can no longer fold back to the source, even if the same filter placed first would have.
- Confusing Reference with Duplicate. Reference stays linked to the original query; Duplicate breaks away immediately as an independent copy.
- Using Remove Errors when Replace Errors was meant. Remove Errors throws away the whole row, not just the failed cell.
Cheat sheet
| Question | Answer |
|---|---|
| Which join kind keeps only matching rows? | Inner |
| Which join kind keeps every row of the first table? | Left Outer |
| Which join kind keeps unmatched rows of the first table only? | Left Anti |
| What matches columns when appending? | Column name, not position |
| What counts rows per group, ignoring their values? | Count Rows |
| Which fill command looks upward for a value? | Fill Down |
| What keeps the row but swaps the failed value? | Replace Errors |
| What pushes steps down to the source as SQL? | Query folding |
| What stays linked to the original query? | Reference |
| What governs whether sources can be combined safely? | Privacy level |
Try it yourself
Quick check
Orders has three rows for customers 101, 102 and 104. Accounts has three rows for customers 101, 102 and 103. If the two are merged on the customer ID with Full Outer, how many rows does the merged query have, and which specific rows are the ones that only exist because of the Full Outer choice?
Show answer
Four rows: the two genuine matches, 101 and 102, plus the unmatched order for 104 and the unmatched account for 103. Those last two rows would not appear under Inner, and only one of them would appear under Left Outer or Right Outer alone, which is exactly why Full Outer is the join kind that keeps everything from both sides.
Frequently asked questions
What is Power Query in Power BI?
Power Query is the data-shaping layer behind a Power BI model, opened from Transform Data on the Home ribbon, where connecting, cleaning and reshaping data is recorded step by step as M code.
What is the difference between Merge Queries and Append Queries?
Merge joins two queries side by side on a matching column, like a SQL join, and the join kind chosen decides which rows survive. Append stacks the rows of two queries on top of each other, matching columns purely by name.
What are the Power Query join kinds?
Inner, Left Outer, Right Outer, Full Outer, Left Anti and Right Anti. Inner keeps only matches; Left and Right Outer each keep every row of one table regardless of a match; Full Outer keeps everything from both; Left and Right Anti keep only the unmatched rows of one table.
What is query folding?
Query folding is Power Query translating its steps into the source’s own query language, usually SQL, so the source does the filtering, grouping and sorting instead of all the raw rows being pulled across first, which is far faster against a large source.
What is a Power Query parameter used for?
A parameter is a single named value that many queries can reference instead of a hard-coded literal, most commonly used to switch an entire set of queries between a Dev and a Prod data source in one edit.
Is Power Query’s M language case sensitive?
Yes, text comparisons such as [Status] = “open” are case sensitive, treating Open and open as different values, unlike an ordinary Excel formula comparison.
Test yourself
Timed questions on Power Query in Power BI, with an explanation for every answer.