Table.Join
Joins two tables on key columns into one flat table: the columns of both, side by side.
Group: Table. Power Query M has it too: a query that uses it is written as in Power BI.
Syntax
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| Parameter | Type | Description |
|---|---|---|
table1 | table | The first (left) table. |
key1 | any | The key column of table1, or a list of key columns. |
table2 | table | The second (right) table. |
key2 | any | The key column of table2, or a list: as many as key1, in the same order. |
joinKind | nullable number optional | JoinKind.Inner when left out; an outer join such as JoinKind.LeftOuter or JoinKind.FullOuter; or an anti join, JoinKind.LeftAnti or JoinKind.RightAnti. |
joinAlgorithm | nullable number optional | A hint on how to join, such as JoinAlgorithm.SortMerge; the result is the same. |
keyEqualityComparers | nullable list optional | Comparers for the keys, one per key column. |
Returns
table. One row per matching pair of rows, with table1's columns and then table2's.
Remarks
- Key columns with the same name on both sides become one column. Any other name the two tables share is an error: rename one side first, or use
Table.NestedJoin, which keeps table2's columns apart in a nested column. - Unlike
Table.NestedJoin, the default kind is an inner join: rows without a match are left out.
Examples
Orders with their price
Table.Join(#table({"Order", "Product"}, {{1, "Chair"}, {2, "Desk"}, {3, "Sofa"}}), "Product", #table({"Name", "Price"}, {{"Chair", 50}, {"Desk", 120}}), "Name")| Order | Product | Name | Price |
|---|---|---|---|
| 1 | Chair | Chair | 50 |
| 2 | Desk | Desk | 120 |
The sofa has no match and an inner join leaves it out.
Every order, matched or not
Table.Join(#table({"Order", "Product"}, {{1, "Chair"}, {2, "Desk"}, {3, "Sofa"}}), "Product", #table({"Name", "Price"}, {{"Chair", 50}, {"Desk", 120}}), "Name", JoinKind.LeftOuter)| Order | Product | Name | Price |
|---|---|---|---|
| 1 | Chair | Chair | 50 |
| 2 | Desk | Desk | 120 |
| 3 | Sofa | null | null |
Keys with the same name
Table.Join(#table({"Order", "Product"}, {{1, "Chair"}, {2, "Desk"}, {3, "Sofa"}}), "Product", #table({"Product", "Price"}, {{"Chair", 50}, {"Desk", 120}}), "Product")| Order | Product | Price |
|---|---|---|
| 1 | Chair | 50 |
| 2 | Desk | 120 |
Product is a key on both sides and comes out once.
Related functions
Table.NestedJoin, Table.FuzzyJoin, Table.Combine, Table.RenameColumns.