Table.AddJoinColumn

Updated on

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]} )
    ]
} )
 */ 

Other functions related to Table.AddJoinColumn are:

BI Gorilla Youtube Channel

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/table-addjoincolumn