Table.AddJoinColumn is a Power Query M function that joins the rows of two tables based on the equality of the values of specified key columns in a nested fashion. The function returns a new table with an added column containing the join results.
Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365
Syntax
Table.AddJoinColumn(
table1 as table,
key1 as any,
table2 as any,
key2 as any,
newColumnName as text,
) as table
Description
Joins the rows of table1
with the rows of table2
based on the equality of the values of the key columns selected by key1
(for table1
) and key2
(for table2
). The results are entered into the column named newColumnName
.
This function behaves similarly to Table.Join with a JoinKind of LeftOuter except that the join results are presented in a nested rather than flattened fashion.
Examples
Add a join column to ( {[saleID = 1, item = “Shirt”], [saleID = 2, item = “Hat”]} ) named “price/stock” from the table ( {[saleID = 1, price = 20], [saleID = 2, price = 10]} ) joined on [saleID].
Table.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"
)
/* 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]} )
]
} )
*/
Related functions
Other functions related to Table.AddJoinColumn are:
2023-2024 © BI Gorilla. All rights are reserved. Information from Microsoft docs is property of Microsoft Corp. | Privacy Policy