Table.NestedJoin

Joins two tables on key columns and puts each row's matches from the second table in a new column, as a table of their own.

Group: Table. Power Query M has it too: a query that uses it is written as in Power BI.

Syntax

Table.NestedJoin(table1 as table, key1 as any, table2 as any, key2 as any, newColumnName as text, optional joinKind as nullable number, optional keyEqualityComparers as nullable list) as table

Parameter	Type	Description
`table1`	table	The table whose rows you keep (the left one).
`key1`	any	The key column of table1, or a list of key columns.
`table2`	any	The table to find the matches in (the right one).
`key2`	any	The key column of table2, or a list of them: as many as key1, in the same order.
`newColumnName`	text	The name of the new column that holds the matches.
`joinKind`	nullable number optional	Which rows to keep: `JoinKind.LeftOuter` when left out, or `JoinKind.Inner`, `JoinKind.LeftAnti` and the other join kinds.
`keyEqualityComparers`	nullable list optional	Comparers for the keys, one per key column.

Returns

table. Table1's columns and one more: in each row, a table of the matching rows of table2.

Remarks

This is the step Merge queries writes in Transform data. Expand the new column with Table.ExpandTableColumn to bring in the columns you need.
With JoinKind.LeftOuter, a row without a match gets an empty table (with table2's columns); with JoinKind.Inner the row is left out; JoinKind.LeftAnti keeps only the rows without one.
Keys match on equal values: "Chair" does not match "chair", and the number 1 does not match the text "1".
The new column's name must not be one of table1's columns.

Examples

Each order's product, nested

Table.NestedJoin(
    #table({"Order", "Product"}, {{1, "Chair"}, {2, "Desk"}, {3, "Sofa"}}), {"Product"},
    #table({"Product", "Price"}, {{"Chair", 50}, {"Desk", 120}}), {"Product"},
    "Products")

Order	Product	Products
1	Chair	Table
2	Desk	Table
3	Sofa	Table

No product row matches the sofa: its cell holds an empty table.

Merge, then expand the price

let
    Merged = Table.NestedJoin(
        #table({"Order", "Product"}, {{1, "Chair"}, {2, "Desk"}, {3, "Sofa"}}), {"Product"},
        #table({"Product", "Price"}, {{"Chair", 50}, {"Desk", 120}}), {"Product"},
        "Products", JoinKind.Inner)
in
    Table.ExpandTableColumn(Merged, "Products", {"Price"})

Order	Product	Price
1	Chair	50
2	Desk	120

Orders whose product is missing

Table.NestedJoin(
    #table({"Order", "Product"}, {{1, "Chair"}, {2, "Desk"}, {3, "Sofa"}}), {"Product"},
    #table({"Product", "Price"}, {{"Chair", 50}, {"Desk", 120}}), {"Product"},
    "Products", JoinKind.LeftAnti)

Order	Product	Products
3	Sofa	Table

Table.Join, Table.ExpandTableColumn, Table.AggregateTableColumn, Table.FuzzyNestedJoin.

Last updated 8 November 2026 · OpenBI Studio 0.3.79