<!-- https://learnsql.cpetoday.com/functions/m/table-nestedjoin -->
# Table.NestedJoin

Language: Power Query M
Category: Table: combine and join
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
Summary: Matches rows from two tables and adds a column that holds the matching rows from the second table, which you can then expand.
Returns: The first table with one new column that holds the matched rows.
Level: Intermediate
Related: Table.ExpandTableColumn, Table.Join, Table.Buffer, Table.SelectRows

## 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
- **table1**: the table to keep. Required.
- **key1**: the key column or columns in table1, in braces for more than one. Required.
- **table2**: the table to match against. Required.
- **key2**: the key column or columns in table2. Required.
- **newColumnName**: the name of the new column of matches, in quotes. Required.
- **joinKind**: `JoinKind.LeftOuter` (the usual choice), `JoinKind.Inner`, `JoinKind.LeftAnti` and others. Optional. The default is a left outer join.
- **keyComparer**: a setting for how keys are compared. Optional.

## 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.

```powerquery
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.

```powerquery
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
- The key types must match. A number key does not match a text key.
- Expand only the columns you need. Each expanded column adds width to the table.
- If the second table has several rows for one key, expanding it multiplies the rows. Check the row count with Table.RowCount before and after.
- Query folding: a merge folds when both tables come from the same database, and the expansion usually folds with it. A merge across different sources does not fold and may trigger a privacy firewall warning.

## 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.
