[Excel/Power Query] Error: Expression.Error — The key didn’t match any rows in the table (merge mismatch) — How to Fix It

Summary

Expression.Error: The key didn't match any rows in the table indicates a merge/join step failed because the keys you selected on the left and right tables don’t actually match. Common causes are type mismatches (Text vs Number), whitespace/casing differences, wrong column order in multi-key merges, or renamed/removed key columns. Fix it by standardizing data types, trimming/cleaning key values, confirming the selected columns (and order) on both sides, and choosing an appropriate join kind (e.g., Left Outer) to preserve base rows while diagnosing null matches.

Context

Power Query merges two tables by comparing key columns. For a successful match, both sides must use the same set of columns, in the same order, with identical data types and comparable values. The error commonly appears on the “Merged Queries” or “Expanded Column” step after a refresh when sources changed schema or formatting. Even subtle differences—like leading/trailing spaces, mixed number/text types, or varying capitalization—can yield no matches. Because merges are often followed by an expand step, the failure may surface there; the root cause is almost always upstream in key alignment.

Probable Cause

  • Data type mismatch. One key column is Text while the other is Number/Date. Power Query won’t coerce them during join.
  • Whitespace/casing differences. Keys differ by leading/trailing spaces or inconsistent capitalization ("ABC" vs "abc ").
  • Wrong key order or missing key. Multi-column merges require identical column selection order on both sides.
  • Missing keys in the lookup table. The right-side table lacks rows for some left-side keys.
  • Schema drift. Column renamed/removed after refresh, so the merge step references a non-existent or unintended column.

Quick Fix

  1. Locate the failing step. In the Power Query Editor, click the “Merged Queries” or subsequent “Expanded Column” step to view the error context.
  2. Standardize key types on both sides. Explicitly convert to the same type (usually Text) to eliminate silent mismatches.
// Convert keys to Text (locale-aware if needed) Left = Table.TransformColumnTypes(LeftSource, {{"Key", type text}}); Right = Table.TransformColumnTypes(RightSource, {{"Key", type text}});
  1. Trim/clean key values. Remove hidden spaces and non-printables before merging.
// Trim + Clean keys LeftClean = Table.TransformColumns(Left, {{"Key", each Text.Trim(Text.Clean(_)), type text}}); RightClean= Table.TransformColumns(Right, {{"Key", each Text.Trim(Text.Clean(_)), type text}});
  1. Rebuild the merge with correct join kind. Use Left Outer to preserve base rows while inspecting unmatched records.
// Merge with consistent key and join kind Joined = Table.NestedJoin( LeftClean, {"Key"}, RightClean, {"Key"}, "RightRows", JoinKind.LeftOuter );
  1. Inspect unmatched rows. Expand the merged column and filter nulls to see which keys didn’t match.
// Expand and find null matches Expanded = Table.ExpandTableColumn(Joined, "RightRows", {"LookupValue"}, {"LookupValue"}); Unmatched = Table.SelectRows(Expanded, each [LookupValue] = null);

Full Example

Scenario: You merge Sales with Products on ProductID. Products stores ProductID as Text (e.g., “00123”), while Sales has it as Number (e.g., 123). The merge returns the error after refresh.

// Sales and Products before normalization Sales = Excel.CurrentWorkbook(){[Name="Sales"]}[Content]; Products = Excel.CurrentWorkbook(){[Name="Products"]}[Content];

// Normalize key types and values
Sales1 = Table.TransformColumnTypes(Sales, {{"ProductID", type text}});
Products1 = Table.TransformColumnTypes(Products, {{"ProductID", type text}});
Sales2 = Table.TransformColumns(Sales1, {{"ProductID", each Text.Trim(Text.Clean()), type text}});
Products2 = Table.TransformColumns(Products1, {{"ProductID", each Text.Trim(Text.Clean()), type text}});

// Merge with Left Outer to keep all Sales
Merged = Table.NestedJoin(
Sales2, {"ProductID"},
Products2, {"ProductID"},
"Prod",
JoinKind.LeftOuter
);

// Expand and check for nulls (unmatched)
Expanded = Table.ExpandTableColumn(Merged, "Prod", {"ProductName", "Category"}, {"ProductName", "Category"});

// Investigate unmatched keys
Unmatched = Table.SelectRows(Expanded, each [ProductName] = null);

Results: If Unmatched returns rows, you have product IDs in Sales that don’t exist (or don’t match) in Products. Fix upstream data or add a mapping/cleaning rule.

Multi-column join (order sensitive): Select keys in the same order on both tables.

// Consistent multi-key join on {"Country","StoreID"} L = Table.TransformColumnTypes(LeftSource, {{"Country", type text}, {"StoreID", type text}}); R = Table.TransformColumnTypes(RightSource, {{"Country", type text}, {"StoreID", type text}});

Join = Table.NestedJoin(L, {"Country","StoreID"}, R, {"Country","StoreID"}, "Right", JoinKind.LeftOuter);

Pitfalls & Debug

  • Symptom → Merge shows zero matches though keys “look” identical. Fix → Explicitly convert both sides to Text and apply Text.Trim/Text.Clean. Watch for leading zeros (“00123” vs “123”).
  • Symptom → Error only after refresh. Fix → Column renamed/removed by source; confirm with Table.ColumnNames() and update the merge step.
  • Symptom → Multi-key merge returns mismatches. Fix → Recreate merge ensuring the same key order on both sides; one column out of order breaks matches.
  • Symptom → Locale/format issues (dates/numbers). Fix → Standardize formatting before typing; consider Using Locale when changing types.
  • Symptom → Large tables with sparse matches. Fix → Pre-filter both sides to the relevant key domain to speed joins and simplify diagnosis.

Validation & Next Steps

Confirm key readiness and join logic before expanding:

// Quick key sanity checks SampleLeftKeys = Table.FirstN(Table.SelectColumns(Sales2, {"ProductID"}), 5); SampleRightKeys = Table.FirstN(Table.SelectColumns(Products2, {"ProductID"}), 5);

// Ensure keys exist
HasKeyLeft = List.Contains(Table.ColumnNames(Sales2), "ProductID");
HasKeyRight = List.Contains(Table.ColumnNames(Products2), "ProductID");

After a successful merge, filter Expanded to null on the lookup columns and quantify unmatched rates. If many keys are missing, revisit upstream transformations (deduping, normalization, or mapping tables). To harden future loads, keep a diagnostic query that lists newly-unmatched keys each refresh.

Sources

Power Query M: Table.NestedJoin (joins)

Text.Trim / Text.Clean

Table.TransformColumnTypes

Labels

Tool/Power Query, OS/Cross-platform, Topic/Merge Keys & Joins