Table.NestedJoin
Table function · By Tahir Nas · Examples run in our simulator · Published · Updated
Joins two tables and puts matches in a nested table column.
When to use it
Use it (Merge Queries) to bring columns from a lookup table, find unmatched rows with LeftAnti, or keep only matches with Inner. Check the lookup key is unique first, or rows multiply.
Syntax
Table.NestedJoin(table1 as table, key1 as any, table2 as any, key2 as any, newColumnName as text, optional joinKind as nullable number) as tableParameters
- joinKind
- JoinKind.LeftOuter (default), Inner, LeftAnti, RightAnti, FullOuter, RightOuter.
Returns
table1 plus a column of matching table2 rows.
Example
Table.ExpandTableColumn(Table.NestedJoin(#table({"Region", "Product", "Qty"}, {{"North", "Kite", 2}, {"South", "Reel", 5}, {"North", "Reel", 1}, {"West", "Kite", 4}}), {"Product"}, #table({"Product", "Price"}, {{"Kite", 20}, {"Reel", 8}}), {"Product"}, "P", JoinKind.LeftOuter), "P", {"Price"})Result
| Region | Product | Qty | Price |
|---|---|---|---|
| North | Kite | 2 | 20 |
| South | Reel | 5 | 8 |
| North | Reel | 1 | 8 |
| West | Kite | 4 | 20 |
Example: find orders with no customer
JoinKind.LeftAnti keeps the left rows that have no match, a quick data-quality check.
Table.RemoveColumns(Table.NestedJoin(#table({"Order", "Cust"}, {{1, "A"}, {2, "Z"}}), {"Cust"}, #table({"Cust"}, {{"A"}}), {"Cust"}, "M", JoinKind.LeftAnti), {"M"})Result
| Order | Cust |
|---|---|
| 2 | Z |
Both results are produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M, and checked on every build.
Good to know
- Duplicate keys in the second table multiply rows.
Common mistakes
- Joining on keys of different types (text 001 versus number 1) and getting no matches.
- Duplicate keys in the lookup table multiplying rows.
The matching command in Excel
- Excel for Windows
- Home > Merge Queries
- What you see
- The Merge dialog sets the two tables, the key columns and the join kind.
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).
Exercises that use Table.NestedJoin
- 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~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.
- advanced~10 minPro
Look up prices by shop and product together (farm shops)
Merge Sales with Prices on both Store and Product, then calculate line revenue.
- intermediate~8 minPro
Flag every order from a repeat customer (kite shop)
Keep every order, and add Repeat Customer = true on all orders of customers who ordered more than once.
- 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.
- 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.
Learn more
- Merge queries: choosing a join kind lesson
- Merge vs append in Power Query: which one do you need? guide
- Reconcile two tables in Power Query (bank vs ledger, system vs system) guide
- Mapping tables vs nested if: categorise values the maintainable way guide
- Compare two snapshots: new, removed and changed rows guide
Related functions
Official documentation: Table.NestedJoin on Microsoft Learn.