Table.Join
TableJoins two tables on key columns, returning a flat combined table.
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 tableParameters
| Name | Type | Required | Description |
|---|---|---|---|
table1 | table | Yes | The left table. |
key1 | any | Yes | The join key column name (text) or list of column names for table1. |
table2 | table | Yes | The right table. |
key2 | any | Yes | The join key column name (text) or list of column names for table2. |
joinKind | number | No | Join type: JoinKind.Inner (0, default), LeftOuter (1), RightOuter (2), FullOuter (3), LeftAnti (4), RightAnti (5). |
joinAlgorithm | number | No | Algorithm hint for performance tuning. |
keyEqualityComparers | list | No | Custom comparers for key matching (e.g., case-insensitive). |
Return Value
table — A table containing columns from both inputs joined on the specified keys.
Remarks
Table.Join is a flat join — it returns a single table with all columns from both inputs side by side, similar to a SQL JOIN. If both tables share a column name (other than the key), the duplicate is renamed with a suffix (e.g. CustomerID.1).
For most Power Query workflows, Table.NestedJoin followed by Table.ExpandTableColumn is preferred because it gives finer control over which columns to expand and avoids automatic column renaming. Use Table.Join when you want a flat result immediately and don't mind resolving column name collisions manually.
When both key columns have the same name, M retains the left table's key and renames the right table's key with .1. Rename or drop the duplicate before exposing the result downstream.
Examples
Example 1: Inner join Sales to Customers
let
Joined = Table.Join(Sales, "CustomerID", Customers, "CustomerID", JoinKind.Inner),
Result = Table.SelectColumns(Joined, {"OrderID", "CustomerID", "Amount", "Name", "Country"})
in
ResultThe final output — selects only the five desired columns (OrderID, CustomerID, Amount, Name, Country) from the joined table, dropping duplicate and unwanted columns.
OrderID | CustomerID | Amount | Name | Country | |
|---|---|---|---|---|---|
| 1 | 1 | C001 | 250 | Alice Johnson | USA |
| 2 | 2 | C002 | 120 | Bob Smith | Canada |
| 3 | 3 | C001 | 75 | Alice Johnson | USA |
| 4 | 4 | C003 | 500 | Carol Davis | USA |
| 5 | 5 | C002 | 90 | Bob Smith | Canada |
| 6 | 6 | C004 | 200 | David Lee | UK |
| 7 | 7 | C001 | 350 | Alice Johnson | USA |
| 8 | 8 | C003 | 160 | Carol Davis | USA |
Example 2: Left anti-join to find unmatched rows
Table.Join(Sales, "CustomerID", Customers, "CustomerID", JoinKind.LeftAnti)OrderID | CustomerID | Amount | Region | Status |
|---|
Compatibility
