Table.Join
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
Spojí řádky table1
s řádky table2
na základě rovnosti hodnot klíčových sloupců vybraných key1
(pro table1
) a key2
(pro table2
).
Ve výchozím nastavení se provádí vnitřní spojení, ale volitelné joinKind
může být zahrnuto k určení typu spojení. Mezi možnosti patří:
- JoinKind.Inner
- JoinKind.LeftOuter
- JoinKind.RightOuter
- JoinKind.FullOuter
- JoinKind.LeftAnti
- JoinKind.RightAnti
- JoinKind.LeftSemi
- JoinKind.RightSemi
K určení způsobu porovnání klíčových sloupců může být zahrnuta volitelná sada keyEqualityComparers
. Tento parametr je aktuálně určen pouze pro interní použití.
Spojí dvě tabulky pomocí jednoho klíčového sloupce.
Využití
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]
}),
"CustomerID"
)
výstup
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]
})
Spojte dvě tabulky, které mají konfliktní názvy sloupců, pomocí více klíčových sloupců.
využití
let
customers = Table.FromRecords({
[TenantID = 1, CustomerID = 1, Name = "Bob", Phone = "123-4567"],
[TenantID = 1, CustomerID = 2, Name = "Jim", Phone = "987-6543"]
}),
orders = Table.FromRecords({
[TenantID = 1, OrderID = 1, CustomerID = 1, Name = "Fishing rod", Price = 100.0],
[TenantID = 1, OrderID = 2, CustomerID = 1, Name = "1 lb. worms", Price = 5.0],
[TenantID = 1, OrderID = 3, CustomerID = 2, Name = "Fishing net", Price = 25.0]
})
in
Table.Join(
customers,
{"TenantID", "CustomerID"},
Table.PrefixColumns(orders, "Order"),
{"Order.TenantID", "Order.CustomerID"}
)
Table.FromRecords({
[TenantID = 1, CustomerID = 1, Name = "Bob", Phone = "123-4567", Order.TenantID = 1, Order.OrderID = 1, Order.CustomerID = 1, Order.Name = "Fishing rod", Order.Price = 100],
[TenantID = 1, CustomerID = 1, Name = "Bob", Phone = "123-4567", Order.TenantID = 1, Order.OrderID = 2, Order.CustomerID = 1, Order.Name = "1 lb. worms", Order.Price = 5],
[TenantID = 1, CustomerID = 2, Name = "Jim", Phone = "987-6543", Order.TenantID = 1, Order.OrderID = 3, Order.CustomerID = 2, Order.Name = "Fishing net", Order.Price = 25]
})