Table.Join
Joins two tables on matching key columns and returns one flat table with the columns of both.
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 keyComparer as nullable function) as tableReturns: A flat table with the columns of both tables.
What it does
Table.Join matches rows from two tables on a key, such as an account code, and puts the columns of both side by side in one flat table. The join kind decides which rows are kept. Table.NestedJoin is the gentler form, because it keeps the second table in a column that you expand only when you want it. Table.Join is the one to use when you want every column at once.
Syntax and arguments
- table1: the first table. Required.
- key1: the key column or columns in the first table. Required.
- table2: the second table. Required.
- key2: the key column or columns in the second table. Required.
- joinKind:
JoinKind.Inner,JoinKind.LeftOuter,JoinKind.RightOuter,JoinKind.FullOuter,JoinKind.LeftAntiorJoinKind.RightAnti. Optional. - joinAlgorithm and keyComparer: optional settings for the match. Leave them out in most cases.
Example
A general ledger is matched to a chart of accounts, to bring the account name and type into every posting. The chart's Account column is renamed first, so the flat join has no duplicate column names.
let
GL = Excel.CurrentWorkbook(){[Name = "GL"]}[Content],
Chart = Excel.CurrentWorkbook(){[Name = "ChartOfAccounts"]}[Content],
ChartKeys = Table.RenameColumns(Chart, {{"Account", "AccountCode"}}),
WithNames = Table.Join(GL, "Account", ChartKeys, "AccountCode", JoinKind.LeftOuter)
in
WithNames
Every GL row keeps its own columns, and the account name and type come across from the chart. A posting with an account not in the chart keeps blank values for those columns.
Example: a second, different use
A reviewer wants only the postings that have a matching account. An inner join removes postings whose account is not in the chart.
let
GL = Excel.CurrentWorkbook(){[Name = "GL"]}[Content],
Chart = Table.RenameColumns(Excel.CurrentWorkbook(){[Name = "ChartOfAccounts"]}[Content], {{"Account", "AccountCode"}}),
Matched = Table.Join(GL, "Account", Chart, "AccountCode", JoinKind.Inner)
in
Matched
Things to watch
- Columns with the same name in both tables clash in a flat join. Rename or prefix one side first.
- Key values must have the same type. Text "1000" and number 1000 do not match.
- If the second table has more than one row for a key, the first table's rows are repeated for each match. The chart of accounts should have one row per account.
- Query folding: a join folds when both tables come from the same database. A join across different sources does not fold, and it may trigger a privacy firewall message.
In business terms
Joining a general ledger to a chart of accounts, invoices to customer details or bank lines to a payment list are everyday reconciliation and reporting steps. Choosing the right join kind decides which exceptions you see.