Table.NestedJoin

Table

Joins two tables on matching key columns and returns the matched rows from the second table as a nested table column.

Syntax

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

Parameters

Name	Type	Required	Description
`table1`	`table`	Yes	The primary (left) table.
`key1`	`any`	Yes	The join key column(s) from the first table.
`table2`	`table`	Yes	The secondary (right) table to join.
`key2`	`any`	Yes	The join key column(s) from the second table.
`newColumnName`	`text`	Yes	Name of the new column that will contain the nested table of matched rows.
`joinKind`	`number`	No	The type of join: JoinKind.Inner, JoinKind.LeftOuter (default), JoinKind.RightOuter, JoinKind.FullOuter, JoinKind.LeftAnti, or JoinKind.RightAnti.

Return Value

table — The first table with a new column containing matched rows from the second table.

Remarks

Table.NestedJoin performs a merge operation between two tables based on matching key columns. The result is the first table with a new column appended, where each cell contains a nested table of the matching rows from the second table.

This is the M equivalent of the "Merge Queries" operation in the Power Query Editor UI. After a nested join, you typically use Table.ExpandTableColumn to bring the desired columns from the nested table into the main table.

The default join kind is JoinKind.LeftOuter, which keeps all rows from the first table and matches from the second. Rows with no match get an empty nested table. Use JoinKind.Inner to keep only rows that have matches in both tables.

For multi-column joins, pass lists for both key parameters: {"Col1", "Col2"}.

Examples

Example 1: Join Sales to Customers

let
    Joined = Table.NestedJoin(
        Table.FirstN(Sales, 4), "CustomerName",
        Customers, "Name",
        "CustomerInfo", JoinKind.LeftOuter
    )
in
    Table.ExpandTableColumn(Joined, "CustomerInfo", {"City"})

Result

	OrderID	CustomerName	Product	Category	UnitPrice	Quantity	OrderDate	Region	City
1	1	Alice	Widget A	Widgets	25	4	1/15/2024	East	New York
2	2	Bob	Gadget B	Gadgets	50	2	1/18/2024	West	Chicago
3	3	Charlie	Widget C	Widgets	15	10	2/1/2024	East	Houston
4	4	Alice	Gadget D	Gadgets	75	1	2/10/2024	North	New York

Compatibility

✓ Power BI Desktop✓ Power BI Service✓ Excel Desktop✓ Excel Online✓ Dataflows✓ Fabric Notebooks