Power QueryExercises

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 table

Parameters

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

RegionProductQtyPrice
NorthKite220
SouthReel58
NorthReel18
WestKite420

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

OrderCust
2Z

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

Common mistakes

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

Learn more

Related functions

Official documentation: Table.NestedJoin on Microsoft Learn.