Analistable

Why Power Query merge returns nulls, and how to fix it

By the Analistable team · Updated · 5 min read

Nulls after expanding a merge mean those rows found no match in the second table. Power Query matches keys exactly: the number 1001 does not match the text “1001”, “AB-1 ” with a trailing space does not match “AB-1”, and “ab-1” does not match “AB-1” because matching is case-sensitive. Set both key columns to the same type, trim and clean them, standardise case, then merge again. Also check that you used Left Outer, not Right or Anti.

Part of our guide: How to merge data in Power BI, Power Query and Tableau

First, confirm it really is a match failure

A merge adds a column of nested tables. With Left Outer, rows that found no match get an empty table, and when you expand it the new columns are null. So nulls are the symptom; the question is why the keys didn't match. The join kinds themselves are explained in Merge Queries join kinds.

The Merge dialog shows a line such as “The selection matches 812 of 1,000 rows from the first table”. Note that number before you click OK. A quick way to list the failures is to repeat the merge with Left Anti: it returns only the rows with no match, which you can inspect side by side with the other table.

A worked example

Orders (first table)
OrderIDCustomerIDAmount
1C-101250
2C-102 120
3c-10375
41104300
Customers (second table)
CustomerIDRegion
C-101North
C-102South
C-103West
1104 (number)East
After a Left Outer merge and expand
OrderIDCustomerIDAmountRegionWhy
1C-101250NorthExact match
2C-102 120nullTrailing space
3c-10375nullLower case
41104300nullText vs number

Only £250 of the £745 in orders gets a region. A report of sales by region would quietly lose two-thirds of the value.

Cause 1: the key columns have different types

If one column is Text and the other Whole Number, nothing matches, even though the values look identical in the preview. Check the type icon at the left of each column header (ABC for text, 123 for whole number). Set both to the same type before the merge, normally Text for IDs so leading zeros survive.

Leading zeros are a trap here: if “00123” is converted to a number it becomes 123 and will not match “00123” in the other table. Convert to Text on both sides, never to Number, when codes can start with 0.

Cause 2: spaces and invisible characters

Exports often add trailing spaces or non-breaking spaces. Select the key column in both tables and use Transform → Format → Trim, then Clean to remove non-printing characters. Trim does not remove non-breaking spaces (character 160) in every case, so replace them explicitly if your data comes from web pages:

CleanKeys = Table.TransformColumns(Orders, {"CustomerID",
    each Text.Upper(Text.Trim(Text.Clean(Text.Replace(Text.From(_), "#(00A0)", " ")))),
    type text})

Cause 3: upper and lower case

Power Query's normal merge is case-sensitive, unlike VLOOKUP and XLOOKUP in Excel. Apply Format → UPPERCASE to the key in both tables. Alternatively, the fuzzy matching options include Ignore case, but fuzzy matching also accepts near matches, so use it only when you want that; see fuzzy merge.

Cause 4: wrong join kind or wrong direction

  • Right Outer keeps every row from the second table, so first-table columns are null where there is no match.
  • Left Anti returns only non-matching rows, so the expanded columns are always null. That is by design.
  • Selecting the tables the wrong way round (lookup on top, data below) makes the lookup table the one that is kept.
  • With multiple key columns, the columns must be selected in the same order in both tables; the small numbers on the headers show the pairing.

Cause 5: the expand step names old columns

The expand step lists the column names it saw when you created it. If the second table's column was later renamed from “Region” to “Sales Region”, the expand step still asks for “Region”. Depending on how the step was written you either get an error or a column of nulls. Open the step's gear icon and tick the current column names again.

Check the result

  1. Count nulls in the expanded column (Column quality in the View ribbon shows the percentage empty).
  2. Compare row counts before and after. A merge with duplicate keys in the second table adds rows, which is a different problem from nulls but often appears at the same time.
  3. Total a value column before and after the merge. They should be equal for a Left Outer merge with unique keys.

Analistable handles the same task with SQL in your browser: it normalises keys with TRIM and UPPER in the query it writes and shows you the SQL, so you can see exactly how rows were matched. For merge work inside Power BI see merging tables in Power BI and the Power BI, Power Query and Tableau guide.

Fix keys once, in a shared step

Rather than cleaning keys separately in every query, create a small function and apply it to the key column in both tables before merging. Then a later change, such as also removing hyphens, happens in one place.

// fnKey: normalise a key value
(v as any) as nullable text =>
    if v = null then null
    else Text.Upper(Text.Trim(Text.Clean(Text.From(v))))

// in each table
Keyed = Table.TransformColumns(Orders, {"CustomerID", fnKey, type text})

Text.From converts numbers to text, so 1104 and "1104" become the same key. Apply the function to both tables, then merge on the cleaned columns. In the worked example above, all four orders then find a region and the merged total equals the £745 in orders.

Frequently asked questions

Is Power Query merge case-sensitive?
Yes, an exact merge is case-sensitive. Convert both key columns to upper or lower case first, or use the Ignore case option under fuzzy matching.
Why does my merge show matches in the dialog but nulls after expanding?
Usually the expand step lists a column name that no longer exists, or you expanded from the wrong merged column. Re-open the expand step and select the current columns.
How do I list the rows that didn't match?
Run the same merge with the Left Anti join kind. It returns only first-table rows without a match.

Related guides