Analistable

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:

Rows returned by each join kind
Join kindRowsContains
Left outer4All 4 orders; C-104's customer columns are null
Right outer43 matched orders + Hart & Co (C-102) with null order columns
Full outer5All orders and all customers
Inner3Only orders whose customer exists
Left anti1O-9004 (customer C-104 not found)
Right anti1Hart & 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

  1. Load both tables as queries.
  2. Home → Merge Queries (or Data → Get Data → Combine Queries → Merge in Excel).
  3. Select the matching column in each table. Ctrl+click to match on several columns — the order you click them pairs them up.
  4. Choose the join kind and click OK.
  5. 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):

Which join kind answers which question
QuestionJoin kind (Aug first, Sep second)Rows
Which suppliers dropped off?Left anti1 (SUP-1)
Which suppliers are new?Right anti2 (SUP-5, SUP-6)
Which continue?Inner3 (SUP-2, SUP-3, SUP-4)
Everything, flaggedFull outer6

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 and fix
SymptomCauseFix
Match count is 0Different data types (Text vs Whole Number) on the keysSet both key columns to Text before merging
Some obvious matches missingTrailing spaces, non-breaking spaces or caseTransform → Format → Trim, Clean and UPPERCASE on both keys
More rows after Left outer than beforeThe second table repeats keysRemove Duplicates on the second table's key, or aggregate it first
Merge is very slow on refreshLarge tables from files, so the join runs locallyRemove 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.

Related guides