Table.Join

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

根据由 key1 (针对 table1)和 key2 (针对 table2)选择的键列的值是否相等,联接 table1 的行与 table2 的行。

默认情况下,将执行内联,但可以包含可选的 joinKind 来指定联接类型。选项包括:

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

可以加入 keyEqualityComparers 的可选集以指定如何比较键列。

示例:

根据 [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")

输出:

Table.FromRecords({[CustomerID=1,Name="Bob",Phone="123-4567",OrderID=1,Item="Fishing rod",Price=100],[CustomerID=1,Name="Bob",Phone="123-4567",OrderID=2,Item="1 lb. worms",Price=5],[CustomerID=2,Name="Jim",Phone="987-6543",OrderID=3,Item="Fishing net",Price=25],[CustomerID=3,Name="Paul",Phone="543-7890",OrderID=4,Item="Fish tazer",Price=200],[CustomerID=3,Name="Paul",Phone="543-7890",OrderID=5,Item="Bandaids",Price=2],[CustomerID=1,Name="Bob",Phone="123-4567",OrderID=6,Item="Tackle box",Price=20]})

results matching ""

    No results matching ""