Downloads
Power Query MTable: combine and joinIntermediate

Table.NestedJoin

Matches rows from two tables and adds a column that holds the matching rows from the second table, which you can then expand.

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

Returns: The first table with one new column that holds the matched rows.

What it does

Table.NestedJoin is the step behind Merge Queries in the Power Query window. It matches each row of the first table to the rows of the second table, and stores the matches in a new column. The new column is a table inside each row. You choose which columns to pull out with Table.ExpandTableColumn, or you can keep the match only to test whether a row has one.

Syntax and arguments

Example

Each sample order is matched to its customer. The customer name and country are brought across, and the new columns are renamed for the report.

let
    Source = Sql.Database("learnsql.cpetoday.com,1433", "classicmodels"),
    Orders = Source{[Schema = "classicmodels", Item = "orders"]}[Data],
    Customers = Source{[Schema = "classicmodels", Item = "customers"]}[Data],
    Merged = Table.NestedJoin(Orders, {"customerNumber"}, Customers, {"customerNumber"}, "Customer", JoinKind.LeftOuter),
    Expanded = Table.ExpandTableColumn(Merged, "Customer", {"customerName", "country"}, {"Customer name", "Customer country"})
in
    Expanded

Every order keeps its row and gains the customer name and country. Because the join is a left outer join, an order with no matching customer is still listed.

Example: a second, different use

A finance team wants the orders that have no matching customer, which can point to bad customer numbers. A left anti join keeps only the unmatched rows.

let
    Source = Sql.Database("learnsql.cpetoday.com,1433", "classicmodels"),
    Orders = Source{[Schema = "classicmodels", Item = "orders"]}[Data],
    Customers = Source{[Schema = "classicmodels", Item = "customers"]}[Data],
    Unmatched = Table.NestedJoin(Orders, {"customerNumber"}, Customers, {"customerNumber"}, "Customer", JoinKind.LeftAnti),
    Missing = Table.RemoveColumns(Unmatched, {"Customer"})
in
    Missing

Things to watch

In business terms

Attaching customer details to invoices, vendor details to bills or budget owners to cost centers is a lookup in the query. The unmatched rows from a left anti join are often the list of data-quality exceptions to fix.

Related functions

All Power Query M functions · View as Markdown · M basics