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
| OrderID | CustomerID | Amount |
|---|---|---|
| 1 | C-101 | 250 |
| 2 | C-102 | 120 |
| 3 | c-103 | 75 |
| 4 | 1104 | 300 |
| CustomerID | Region |
|---|---|
| C-101 | North |
| C-102 | South |
| C-103 | West |
| 1104 (number) | East |
| OrderID | CustomerID | Amount | Region | Why |
|---|---|---|---|---|
| 1 | C-101 | 250 | North | Exact match |
| 2 | C-102 | 120 | null | Trailing space |
| 3 | c-103 | 75 | null | Lower case |
| 4 | 1104 | 300 | null | Text 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
- Count nulls in the expanded column (Column quality in the View ribbon shows the percentage empty).
- 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.
- 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.