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

Table.Join

Owen Duncan|Last Updated: 10/5/2018
|
1 Contributor

Table.Join(table1 as table, key1 as any, table2 as table, key2 as any, optional joinKind as nullable number, optional joinAlgorithm as nullable number, optional keyEqualityComparers as nullable list) as table

About

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

By default, an inner join is performed, however an optional joinKind may be included to specify the type of join. Options include:

  • JoinKind.Inner
  • JoinKind.LeftOuter
  • JoinKind.RightOuter
  • JoinKind.FullOuter
  • JoinKind.LeftAnti
  • JoinKind.RightAnti

An optional set of keyEqualityComparers may be included to specify how to compare the key columns.

Example 1

Inner join the two tables on [CustomerID]

Table.Join
(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"]}), 
"CustomerID", 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]}), "CustomerID")
CustomerIDNamePhoneOrderIDItemPrice
1Bob123-45671Fishing rod100
1Bob123-456721 lb. worms5
2Jim987-65433Fishing net25
3Paul543-78904Fish tazer200
3Paul543-78905Bandaids2
1Bob123-45676Tackle box20
© 2018 Microsoft