Table.AddJoinColumn
Merges the rows of table1 with the rows of table2 using the key columns selected by key1 (for table1) and key2 (for table2) to determine equality. The output is stored in a new column called newColumn...
Table.AddJoinColumn(table1 as table, key1 as any, table2 as function, key2 as any, newColumnName as text) as tableMerges the rows of table1 with the rows of table2 using the key columns selected by key1 (for table1) and key2 (for table2) to determine equality. The output is stored in a new column called newColumnName. This function is similar to Table.Join with a LeftOuter JoinKind, but the results are displayed in a nested format instead of a flattened format.
Integrate a column called "price/stock" from the given table ({[saleID = 1, item = "Shirt"], [saleID = 2, item = "Hat"]}) with another table ({[saleID = 1, price = 20], [saleID = 2, price = 10]}) using a join on [saleID].
Power Query M Language:
MTable.AddJoinColumn(
Table.FromRecords({
[saleID = 1, item = "Shirt"],
[saleID = 2, item = "Hat"]
}),
"saleID",
() => Table.FromRecords({
[saleID = 1, price = 20, stock = 1234],
[saleID = 2, price = 10, stock = 5643]
}),
"saleID",
"price/stock"
)
Resulting Output:
Table.FromRecords({
[
saleID = 1,
item = "Shirt",
price = Table.FromRecords({[saleID = 1, price = 20, stock = 1234]})
],
[
saleID = 2,
item = "Hat",
price = Table.FromRecords({[saleID = 2, price = 10, stock = 5643]})
]
})