Table.AddJoinColumn
TableAdds a column of nested tables from a second table by matching each row by key.
Syntax
Table.AddJoinColumn(table1 as table, key1 as any, table2 as function, key2 as any, newColumnName as text) as tableParameters
| Name | Type | Required | Description |
|---|---|---|---|
table1 | table | Yes | The left table to add a column to. |
key1 | any | Yes | A column name or list of column names from table1 used as the join key. |
table2 | function | Yes | A function or table providing the right-side data to match against. |
key2 | any | Yes | A column name or list of column names from table2 used as the join key. |
newColumnName | text | Yes | The name for the new nested table column. |
Return Value
table — The original table with a new column containing nested tables of matching rows from table2.
Remarks
Table.AddJoinColumn adds a column of nested tables to table1 by matching each row's key against table2. The table2 parameter is typed as function in the signature because this function was designed for custom connector scenarios where the right-side data may be a function invoked per key rather than a static table. In practice, you can pass an actual table and the function behaves like a left outer nested join.
For standard table-to-table joins in Power Query, Table.NestedJoin is preferred because it is more legible and has the same semantics. Reserve Table.AddJoinColumn for situations where the data connector pattern requires it, such as when building a connector that pages right-side data on demand.
Each cell in the new nested column contains a table of all matching rows from table2. Use Table.ExpandTableColumn to flatten these nested tables into additional columns on table1. Rows with no match produce an empty nested table (not null), so the expansion yields no rows for that key rather than an error.
Examples
Example 1: Add a nested join column using Sales and Customers
let
Sales = #table(
{"OrderID", "CustomerName", "Product", "UnitPrice"},
{
{1, "Alice", "Widget A", 25.00},
{2, "Bob", "Gadget B", 50.00},
{3, "Alice", "Widget C", 15.00}
}
),
Customers = #table(
{"Name", "City"},
{{"Alice", "New York"}, {"Bob", "Chicago"}}
),
Result = Table.AddJoinColumn(Sales, "CustomerName", Customers, "Name", "CustomerInfo")
in
ResultOrderID | CustomerName | Product | UnitPrice | CustomerInfo | |
|---|---|---|---|---|---|
| 1 | 1 | Alice | Widget A | 25 | [Table] |
| 2 | 2 | Bob | Gadget B | 50 | [Table] |
| 3 | 3 | Alice | Widget C | 15 | [Table] |
Example 2: Expand the nested column to get city data
let
Sales = #table(
{"OrderID", "CustomerName"},
{{1, "Alice"}, {2, "Bob"}, {3, "Charlie"}}
),
Customers = #table(
{"Name", "City", "State"},
{{"Alice", "New York", "NY"}, {"Bob", "Chicago", "IL"}, {"Charlie", "Houston", "TX"}}
),
Joined = Table.AddJoinColumn(Sales, "CustomerName", Customers, "Name", "CustomerInfo"),
Expanded = Table.ExpandTableColumn(Joined, "CustomerInfo", {"City", "State"})
in
ExpandedOrderID | CustomerName | City | State | |
|---|---|---|---|---|
| 1 | 1 | Alice | New York | NY |
| 2 | 2 | Bob | Chicago | IL |
| 3 | 3 | Charlie | Houston | TX |
Example 3: Join on multiple key columns
let
Orders = #table(
{"Region", "Product", "Qty"},
{{"East", "Widget A", 4}, {"West", "Gadget B", 2}}
),
Prices = #table(
{"Region", "Product", "Price"},
{{"East", "Widget A", 25.00}, {"West", "Gadget B", 50.00}}
),
Result = Table.AddJoinColumn(Orders, {"Region", "Product"}, Prices, {"Region", "Product"}, "PriceInfo")
in
ResultRegion | Product | Qty | PriceInfo | |
|---|---|---|---|---|
| 1 | East | Widget A | 4 | [Table] |
| 2 | West | Gadget B | 2 | [Table] |