New: 6 free SQL practice datasets with 300+ questions — try the SQL Compiler →
Power BI

Power Query in Power BI Explained

Power Query in Power BI explained: the six merge join kinds, Group By, unpivot, fill down and up, parameters and query folding, with real row counts.

Upskly AI Team October 5, 2026 12 min read
Power Query in Power BI Explained

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

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.

Same two tables (Orders x Accounts), six join kinds, six different row counts
Join kindRows keptWhat survives
Inner2Only rows with a match on both sides: customers 101 and 102
Left Outer3Every row of the first table (Orders), matched or not; order 104 gets a blank account
Right Outer3Every row of the second table (Accounts), matched or not; account 103 gets no order
Full Outer4Everything from both sides: the two matches, plus the unmatched order and the unmatched account
Left Anti1Only rows of the first table with no match in the second: just order 104
Right Anti1Only 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:

Grouping the same data two different ways
AggregationNS
Sum of Revenue225 (120 + 45 + 60)280 (80 + 200)
Count Rows32

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

Seven reshaping steps, each proven against a real small table
TransformWhat it doesWorked example
Unpivot Other ColumnsTurns wide columns into row pairs of attribute and valueProduct/Q1/Q2 becomes Product, Quarter, Value; the first row produced pairs Lamp with Q1 and 5, before Lamp/Q2, Mug/Q1, Mug/Q2
Fill DownCopies the last seen value downward into every blank below itFruit, blank, blank, Veg, blank becomes Fruit, Fruit, Fruit, Veg, Veg
Fill UpCopies the next seen value upward into every blank above itBlank, blank, Fruit, blank, Veg becomes Fruit, Fruit, Fruit, Veg, Veg
Split Column by delimiterCuts each value at the delimiter into separate columns, row by rowAsha|Pune and Ravi|Mumbai, split on “|”, give Asha, Pune, Ravi, Mumbai
Add Index ColumnAdds a running number, with a chosen start and incrementStarting at 1, incrementing by 2, over three rows gives 1, 3, 5
Add Custom ColumnAdds a column computed from an M expression, such as nested if/then/elseAmount 40, 120, 75 tiered at 100 and 50 gives Low, High, Mid, in that row order
Transform ColumnsApplies one function to every row of each listed column at onceText.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:

Replace Errors keeps the row; Remove Errors throws it away
CommandWhat happens to the error
Replace ErrorsKeeps the row, substituting a chosen value, such as -1, into just the failed cell
Remove ErrorsDeletes 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

Four ways to manage a growing set of queries
ActionWhat it does
ReferenceBuilds a new query that starts from the first query's finished result, and stays linked: a later change to the original flows through
DuplicateCopies every step into a new, fully independent query that can diverge freely from the original
Disable Enable LoadKeeps 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 viewA 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

Quick answers
QuestionAnswer
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.

Take the Quiz
Upskly AI Team
Learning made simple
Scroll to Top