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  

ArgumentDescription
table1The Table to modify.
key1The table1 column to join.
table2The Table to check against.
key2The table2 column to join.
newColumnNameThe 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"  

CustomerIDNamePhoneOrders.OrderIDOrders.CustomerIDOrders.ItemOrders.Price
1Bob123-456711Fishing rod100
1Bob123-4567211 lb. worms5
2Jim987-654332Fishing net25
3Paul543-789043Fish tazer200
3Paul543-789053Bandaids2
1Bob123-456761Tackle box20
4Ringo232-1550
Show: