Merge queries: choosing a join kind
Pick the join kind that returns exactly the rows the question asks for.
~12 min read · intermediate · Module 12: Merging (joining) tables
By Tahir Nas · Examples run in our simulator · Published · Updated
Before this: Append tables and align their columns
A merge matches rows of one table with rows of another on key columns, then adds the matches as a nested table you can expand. The join kind decides which rows survive. Here are three orders and three customers; order A-3 has an unknown customer C9, and customer C3 has no orders.
let
Orders = #table({"OrderID", "Cust"}, {{"A-1", "C1"}, {"A-2", "C2"}, {"A-3", "C9"}}),
Customers = #table({"Cust", "City"}, {{"C1", "Leeds"}, {"C2", "York"}, {"C3", "Hull"}}),
Count = (kind) => Table.RowCount(Table.NestedJoin(Orders, {"Cust"}, Customers, {"Cust"}, "M", kind))
in
#table({"Join kind", "Rows"}, {
{"Left outer", Count(JoinKind.LeftOuter)},
{"Inner", Count(JoinKind.Inner)},
{"Left anti", Count(JoinKind.LeftAnti)},
{"Right anti", Count(JoinKind.RightAnti)},
{"Full outer", Count(JoinKind.FullOuter)},
{"Right outer", Count(JoinKind.RightOuter)}
})Result
| Join kind | Rows |
|---|---|
| Left outer | 3 |
| Inner | 2 |
| Left anti | 1 |
| Right anti | 1 |
| Full outer | 4 |
| Right outer | 3 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
| Join kind | Keeps | Typical use |
|---|---|---|
| Left outer | All left rows, matches where found | Lookups |
| Inner | Only rows with a match | Intersections |
| Left anti | Left rows with no match | Data-quality checks |
| Right anti | Right rows with no match | Unused master records |
| Full outer | Everything from both | Reconciliations |
| Right outer | All right rows, matches where found | Rarely needed: swap the tables and use left outer |
A lookup
let
Orders = #table({"OrderID", "Cust"}, {{"A-1", "C1"}, {"A-2", "C2"}, {"A-3", "C9"}}),
Customers = #table({"Cust", "City"}, {{"C1", "Leeds"}, {"C2", "York"}, {"C3", "Hull"}}),
Merged = Table.NestedJoin(Orders, {"Cust"}, Customers, {"Cust"}, "Customer", JoinKind.LeftOuter),
Expanded = Table.ExpandTableColumn(Merged, "Customer", {"City"}, {"City"})
in
ExpandedResult
| OrderID | Cust | City |
|---|---|---|
| A-1 | C1 | Leeds |
| A-2 | C2 | York |
| A-3 | C9 | null |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Expand only the columns you need, and untick Use original column name as prefix unless you want names like Customer.City. The null City for A-3 is information: the customer is missing from the master list.
Finding what's missing
let
Orders = #table({"OrderID", "Cust"}, {{"A-1", "C1"}, {"A-2", "C2"}, {"A-3", "C9"}}),
Customers = #table({"Cust", "City"}, {{"C1", "Leeds"}, {"C2", "York"}, {"C3", "Hull"}}),
Unmatched = Table.NestedJoin(Orders, {"Cust"}, Customers, {"Cust"}, "Customer", JoinKind.LeftAnti)
in
Table.RemoveColumns(Unmatched, {"Customer"})Result
| OrderID | Cust |
|---|---|
| A-3 | C9 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Where to find this in Excel
- Excel for Windows
- Home > Merge Queries (Combine group)
- What you see
- The Merge dialog shows the left and right tables, the matching columns and a Join Kind list with six options.
Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Merge queries overview (Microsoft Learn).
Common mistakes
- Using an inner join for a lookup, silently dropping orders whose customer is missing.
- Merging on keys of different types (number vs text) or with stray spaces, so nothing matches.
- Expanding every column with the prefix option on, then referring to the wrong names later.
Excel, Power BI and the simulator
- Excel and Power BI Desktop: Home → Merge Queries (adds a step) or Merge Queries as New. The dialog shows how many rows matched, which is a useful first check.
- The merge dialog's fuzzy matching option uses Table.FuzzyNestedJoin. The simulator doesn't run fuzzy merges; they raise UnsupportedFeature.
- Right and full outer joins in the simulator list matched left rows first, then unmatched right rows (KD04). Real hosts don't guarantee an order, so sort afterwards if it matters.
Next step
Apply this lesson in the simulator. The exercise is checked against fresh data, so the query has to work for rows it hasn't seen. It's part of Pro.
Practise: Look up each order's customer city (kite shop)Practice this
Each exercise runs in the browser and is checked against fresh data, not just the example.
- intermediate~9 minPro
Look up each order's customer city (kite shop)
Merge Orders with Customers on Customer and expand City, keeping every order.
- intermediate~9 minPro
Look up each order's customer city (bakery)
Merge Orders with Customers on Customer and expand City, keeping every order.
- intermediate~9 minPro
Look up each order's customer city (bike rental)
Merge Orders with Customers on Customer and expand City, keeping every order.
- intermediate~8 minPro
Find orders from unknown customers (kite shop)
Use a left anti join to list orders whose customer isn't in the customer master.
- intermediate~8 minPro
Find orders from unknown customers (bakery)
Use a left anti join to list orders whose customer isn't in the customer master.
- intermediate~8 minPro
Find orders from unknown customers (bike rental)
Use a left anti join to list orders whose customer isn't in the customer master.
- beginner~6 minPro
Keep only enrolments for courses that are still running (college)
Enrolments refer to course codes; the Courses table lists only courses still running. Keep matching enrolments with their title.
- beginner~6 minPro
Keep only enrolments for courses that are still running (academy)
Enrolments refer to course codes; the Courses table lists only courses still running. Keep matching enrolments with their title.
- advanced~14 minPro
Reconcile bank and ledger with a full outer join (charity)
Match bank lines to ledger entries by Ref and flag what's only in the bank, only in the ledger, or in both.
- advanced~14 minPro
Reconcile bank and ledger with a full outer join (studio)
Match bank lines to ledger entries by Ref and flag what's only in the bank, only in the ledger, or in both.
- intermediate~7 minPro
Find products that were never ordered (toy shop)
List catalogue products with no order lines at all, so the buyer can review them.
- intermediate~7 minPro
Find products that were never ordered (stationery)
List catalogue products with no order lines at all, so the buyer can review them.
- advanced~11 minPro
Summarise each customer's orders without multiplying rows (florist)
Join customers to their orders but keep one row per customer, with the number of orders and total quantity.
- advanced~11 minPro
Summarise each customer's orders without multiplying rows (printer)
Join customers to their orders but keep one row per customer, with the number of orders and total quantity.
- intermediate~8 minPro
Expand a one-to-many merge and keep childless parents (agency)
List every task with its project's owner, and keep projects that have no tasks yet as a single row.
- intermediate~8 minPro
Expand a one-to-many merge and keep childless parents (renovation)
List every task with its project's owner, and keep projects that have no tasks yet as a single row.
- intermediate~9 minPro
Clean both keys before merging (deli)
Customer names in Orders and Customers differ in case and stray spaces, so the merge misses matches. Clean the keys and merge.
- intermediate~9 minPro
Clean both keys before merging (cycles)
Customer names in Orders and Customers differ in case and stray spaces, so the merge misses matches. Clean the keys and merge.
- advanced~20 minPro
Project: revenue by regional manager from a raw export (kite shop)
From a messy CSV export and a manager list, produce completed revenue per regional manager, rounded to cents and sorted.
- advanced~20 minPro
Project: revenue by regional manager from a raw export (bakery)
From a messy CSV export and a manager list, produce completed revenue per regional manager, rounded to cents and sorted.
- advanced~20 minPro
Project: revenue by regional manager from a raw export (bike rental)
From a messy CSV export and a manager list, produce completed revenue per regional manager, rounded to cents and sorted.
- intermediate~9 minPro
Predict how many rows each order becomes after a merge (kite shop)
Before merging Orders with Shipments (left outer) and expanding, predict the number of rows per order.
- intermediate~9 minPro
Predict how many rows each order becomes after a merge (bakery)
Before merging Orders with Shipments (left outer) and expanding, predict the number of rows per order.
- intermediate~9 minPro
Predict how many rows each order becomes after a merge (bike rental)
Before merging Orders with Shipments (left outer) and expanding, predict the number of rows per order.