Table.NestedJoin Function in Power Query

The Table.NestedJoin function is used to combine two tables by joining the rows of the tables based on the equality of the keys. The result is a new table where a column is added containing nested tables of matching rows from the second table.

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

The function has the following parameters:

Parameter	Description
table1	The first table (left table).
key1	The key column(s) from table1 used to match rows.
table2	The second table (right table).
key2	The key column(s) from table2 used to match rows.
newColumnName	The name of the new column containing the nested tables.
optionalJoinKind	The type of join to perform (optional).

Example:

Power Query M

let
    Customers = Table.FromRecords({
        [CustomerID = 1, Name = "Ashish"],
        [CustomerID = 2, Name = "Alia"],
        [CustomerID = 3, Name = "Jaqueline"]
    }),
    Orders = Table.FromRecords({
        [CustomerID = 1, OrderID = 1001],
        [CustomerID = 1, OrderID = 1002],
        [CustomerID = 2, OrderID = 1003],
        [CustomerID = 4, OrderID = 1004]
    }),
    JoinedTable = Table.NestedJoin(Customers, {"CustomerID"}, Orders, {"CustomerID"}, "OrderDetails", JoinKind.LeftOuter),
    return = Table.AddColumn(JoinedTable, "Matched Rows", each Table.RowCount([OrderDetails]))
in
    return

The output of the above code is shown below:

Result:

CustomerID	Name	OrderDetails
1	Ashish	Table with 2 rows (OrderID 1001, 1002)
2	Alia	Table with 1 row (OrderID 1003)
3	Jaqueline	Empty table (no matching orders)

Previous Next