Table.AddJoinColumn
This topic applies to the Power Query Formula Language which can be used with Power Query and Power BI Desktop to build queries that mashup data. See the list of function categories.
Performs a nested join between table1 and table2 from specific columns and produces the join result as a newColumnName column for each row of table1.
Table.AddJoinColumn(table1 as table, key1 as any, table2 as function, key2 as any, newColumnName as text) as table
| Argument | Description |
|---|---|
| table1 | The Table to modify. |
| key1 | The table1 column to join. |
| table2 | The Table to check against. |
| key2 | The table2 column to join. |
| newColumnName | The name of the new column. |
Table.AddJoinColumn is similar to Table.Join except that the join results are presented in a nested rather than flattened table.
Table.AddJoinColumn performs a left outer join by default, other join types are supported in Table.Join or Table.NestedJoin
The type of the resulting table is computed by taking the type of table1 and appending a column newColumnName with a type that is the type of table2.
For more information about joining tables, see Table.Join.
let
Query = let
Customers = Table.FromRecords({
[CustomerID = 1, Name = "Bob", Phone = "123-4567"],
[CustomerID = 2, Name = "Jim", Phone = "987-6543"],
[CustomerID = 3, Name = "Paul", Phone = "543-7890"],
[CustomerID = 4, Name = "Ringo", Phone = "232-1550"]
}),
Orders = Table.FromRecords({
[OrderID = 1, CustomerID = 1, Item = "Fishing rod", Price = 100.0],
[OrderID = 2, CustomerID = 1, Item = "1 lb. worms", Price = 5.0],
[OrderID = 3, CustomerID = 2, Item = "Fishing net", Price = 25.0],
[OrderID = 4, CustomerID = 3, Item = "Fish tazer", Price = 200.0],
[OrderID = 5, CustomerID = 3, Item = "Bandaids", Price = 2.0],
[OrderID = 6, CustomerID = 1, Item = "Tackle box", Price = 20.0],
[OrderID = 7, CustomerID = 5, Item = "Bait", Price = 3.25],
[OrderID = 8, CustomerID = 5, Item = "Fishing Rod", Price = 100.0],
[OrderID = 9, CustomerID = 6, Item = "Bait", Price = 3.25]
})
in
Table.AddJoinColumn(
Customers, {"CustomerID"},
Orders, {"CustomerID"},
"Orders"
),
#"Expand Orders" = Table.ExpandTableColumn(Query, "Orders", {"OrderID", "CustomerID", "Item", "Price"}, {"Orders.OrderID", "Orders.CustomerID", "Orders.Item", "Orders.Price"})
in
#"Expand Orders"
| CustomerID | Name | Phone | Orders.OrderID | Orders.CustomerID | Orders.Item | Orders.Price |
|---|---|---|---|---|---|---|
| 1 | Bob | 123-4567 | 1 | 1 | Fishing rod | 100 |
| 1 | Bob | 123-4567 | 2 | 1 | 1 lb. worms | 5 |
| 2 | Jim | 987-6543 | 3 | 2 | Fishing net | 25 |
| 3 | Paul | 543-7890 | 4 | 3 | Fish tazer | 200 |
| 3 | Paul | 543-7890 | 5 | 3 | Bandaids | 2 |
| 1 | Bob | 123-4567 | 6 | 1 | Tackle box | 20 |
| 4 | Ringo | 232-1550 |