Table of contents
TOC
Collapse the table of content
Expand the table of content

Table.AddJoinColumn

Owen Duncan|Last Updated: 4/19/2018

About

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  

Arguments

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.

Remarks

  • 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.

Example

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
© 2018 Microsoft