Power Query Merge Queries: every join kind explained
By the Analistable team · Updated · 5 min read
Power Query's Merge dialog offers six join kinds. Left outer keeps every row of the first table (the default). Right outer keeps every row of the second. Full outer keeps everything. Inner keeps only matches. Left anti returns first-table rows with no match, and Right anti second-table rows with no match — ideal for finding missing records.
Part of our guide: How to merge data in Power BI, Power Query and Tableau
The example
Orders has customer IDs C-101, C-103, C-101 and C-104. Customers has C-101, C-102 and C-103. Merging Orders (first) with Customers (second) on Customer ID:
| Join kind | Rows | Contains |
|---|---|---|
| Left outer | 4 | All 4 orders; C-104's customer columns are null |
| Right outer | 4 | 3 matched orders + Hart & Co (C-102) with null order columns |
| Full outer | 5 | All orders and all customers |
| Inner | 3 | Only orders whose customer exists |
| Left anti | 1 | O-9004 (customer C-104 not found) |
| Right anti | 1 | Hart & Co (no orders) |
When to use each
- Left outer: add lookup columns to a main table without losing rows (the most common).
- Inner: analyse only complete records.
- Full outer: reconcile two lists and see everything that's in either.
- Left anti / Right anti: data quality and reconciliation — orders with unknown customers, customers without orders, items in last month's list but not this month's.
- Right outer: rarely needed; swap the table order and use left outer instead, which is easier to read.
Doing the merge
- Load both tables as queries.
- Home → Merge Queries (or Data → Get Data → Combine Queries → Merge in Excel).
- Select the matching column in each table. Ctrl+click to match on several columns — the order you click them pairs them up.
- Choose the join kind and click OK.
- Expand the new column. For anti joins, there's nothing to expand: remove the column and keep the rows.
The M function
= Table.NestedJoin(Orders, {"Customer ID"}, Customers, {"Customer ID"}, "Customers", JoinKind.LeftAnti)The JoinKind values are LeftOuter, RightOuter, FullOuter, Inner, LeftAnti and RightAnti. Power Query's key matching is case-sensitive: “c-101” won't match “C-101”. Clean keys with Text.Upper and Text.Trim first.
For names that are spelled slightly differently, use fuzzy merge. For the same concepts outside Power Query, see joining spreadsheets.
Check the match count
The Merge dialog shows how many rows of the first table found a match (for example “The selection matches 3 of 4 rows from the first table”). Read it before clicking OK. A much lower number than expected nearly always means the keys need cleaning.
Worked example: reconciling two lists
Join kinds earn their keep in reconciliation. Take last month's supplier list (Suppliers_Aug: SUP-1, SUP-2, SUP-3, SUP-4) and this month's (Suppliers_Sep: SUP-2, SUP-3, SUP-4, SUP-5, SUP-6):
| Question | Join kind (Aug first, Sep second) | Rows |
|---|---|---|
| Which suppliers dropped off? | Left anti | 1 (SUP-1) |
| Which suppliers are new? | Right anti | 2 (SUP-5, SUP-6) |
| Which continue? | Inner | 3 (SUP-2, SUP-3, SUP-4) |
| Everything, flagged | Full outer | 6 |
In the Full outer result, a null in either table's key column tells you which side the supplier is missing from. Add a custom column such as if [SupplierID] = null then "New" else if [Suppliers_Sep.SupplierID] = null then "Dropped" else "Both" to label each row.
Merging on more than one column
When one column isn't unique (a SKU sold in several stores), select several columns in each table. Ctrl+click Store then SKU in the first table, then Store then SKU in the second; small numbers appear on the headers to show the pairing. In M the key lists simply get longer:
= Table.NestedJoin(Sales, {"Store", "SKU"}, Stock, {"Store", "SKU"}, "Stock", JoinKind.LeftOuter)Pairing in the wrong order (Store with SKU) produces no matches, and the match count at the bottom of the dialog drops to zero, which is the warning sign.
Troubleshooting
| Symptom | Cause | Fix |
|---|---|---|
| Match count is 0 | Different data types (Text vs Whole Number) on the keys | Set both key columns to Text before merging |
| Some obvious matches missing | Trailing spaces, non-breaking spaces or case | Transform → Format → Trim, Clean and UPPERCASE on both keys |
| More rows after Left outer than before | The second table repeats keys | Remove Duplicates on the second table's key, or aggregate it first |
| Merge is very slow on refresh | Large tables from files, so the join runs locally | Remove unneeded columns before the merge; buffer the small table with Table.Buffer |
Common mistakes
- Choosing the tables in the wrong order. Left and right refer to the first and second table in the dialog, so swapping them swaps the meaning of Left anti and Right anti.
- Expanding every column. Untick “Use original column name as prefix” only if names won't clash, and bring across only the columns you need.
- Forgetting that a Full outer join leaves the key in two columns. Combine them with a custom column (
if [ID] = null then [Other.ID] else [ID]) before removing either. - Expecting the merge to update when a new column appears in the second table: the expand step lists columns by name.
To reuse the join every month, keep both tables as queries pointed at stable file paths or folders and simply refresh. The same six joins in formulas and SQL are compared in joining two tables in Excel.
Frequently asked questions
- What is the default join kind in Power Query?
- Left outer: all rows from the first table and matching rows from the second.
- How do I find rows that don't match in Power Query?
- Use a Left anti join (rows in the first table with no match) or a Right anti join (rows in the second table with no match).
- Is Power Query merge case-sensitive?
- Yes for exact merges. Clean both key columns to the same case, or use fuzzy matching with Ignore case.
- How do I merge on two columns in Power Query?
- Ctrl+click the columns in the same order in both tables in the Merge dialog. The numbers on the column headers show which columns are paired.
- Which join kind shows records missing from either list?
- Full outer. Rows with null in the first table's key are only in the second table, and rows with null in the second table's key are only in the first.