Fix 'Expression.Error: The key didn't match any rows in the table' in Power Query Excel

TL;DR

Power Query looked for a key, found nothing, and threw an error instead of returning null. Nine times out of ten, the culprit is a data type mismatch — one column is text, the other is number or date. Cast both sides to the same type before the merge or lookup step and the error goes away.

Where This Error Shows Up

The error can surface in several places, not just one:

• Manual lookups using Table.SelectRows or Record.Field in M code
• Drilling into a nested table column after a Merge Queries step
• The row-index lookup pattern: lookupTable{[ID = someValue]}
• Custom M functions that reference a specific row by key

Root Cause

The M syntax tableName{[ColumnName = value]} expects exactly one matching row. Find zero? Error. Find two? Also an error. There's no graceful fallback built in.

What makes this tricky: the lookup is type-sensitive. The string "123" and the number 123 look identical in a cell but are completely different to Power Query — a mismatch there silently kills the match. Other common triggers:

• Source data updated and a previously valid key got deleted or renamed
• Leading or trailing whitespace hiding in one column (common with CSV imports)
• Dates stored as text ("2024-01-15") vs. actual date type values
• Column name typos in M code — column names are case-sensitive

Fix 1: Match Data Types on Both Sides

Open the Advanced Editor and find the lookup line. Force both sides to the same type before comparing:

// Before — breaks when ID is number in one table, text in the other
let
    result = lookupTable{[ID = sourceID]}[Value]
in
    result

// After — cast everything to text first
let
    safeID = Text.From(sourceID),
    safeTable = Table.TransformColumnTypes(lookupTable, {{"ID", type text}}),
    result = safeTable{[ID = safeID]}[Value]
in
    result

If the key is always numeric (like an order ID or employee number), cast to number instead:

let
    numID = Number.From(sourceID),
    numTable = Table.TransformColumnTypes(lookupTable, {{"ID", type number}}),
    result = numTable{[ID = numID]}[Value]
in
    result

Fix 2: Use try…otherwise to Handle Missing Keys Gracefully

Not every source row will have a match — that's normal in real data. Rather than letting the query crash, wrap the lookup in try:

let
    safeLookup = try lookupTable{[ID = sourceID]}[Value] otherwise null
in
    safeLookup

Missing key → returns null, not an error. Same idea as wrapping an Excel formula in IFERROR. Once you load the data, filter the null rows to see exactly which keys had no match.

Fix 3: Trim Whitespace Before Comparing

CSV exports and copy-pasted data are notorious for invisible spaces. "ABC " and "ABC" are not the same key. Strip both sides:

let
    cleanSource = Table.TransformColumns(sourceTable, {{"ID", Text.Trim}}),
    cleanLookup = Table.TransformColumns(lookupTable, {{"ID", Text.Trim}}),
    merged = Table.NestedJoin(
        cleanSource, "ID",
        cleanLookup, "ID",
        "LookupResult", JoinKind.LeftOuter
    )
in
    merged

Fix 4: Validate Keys Exist Before Lookup

Inside a custom function that receives a key parameter, check existence first rather than assuming:

(inputID as text) =>
let
    matches = Table.SelectRows(lookupTable, each [ID] = inputID),
    result = if Table.RowCount(matches) > 0
             then matches{0}[Value]
             else "KEY NOT FOUND"
in
    result

Table.SelectRows returns an empty table when nothing matches — no error thrown. The row-index syntax {[ID = ...]} doesn't give you that safety net, so this pattern is more robust for user-facing queries.

Fix 5: Check Merge Join Type in the UI

Using the Merge Queries dialog and the error only appears when you expand the merged column? The join type is likely wrong:

• Click the merge step in Applied Steps
• Look at the Join Kind setting — Inner Join silently drops rows with no match, which breaks downstream steps that assume all rows are present
• Switch to Left Outer Join to keep every source row (unmatched rows get null in the lookup columns)
• Filter out nulls afterward if you need to exclude them

Verification Steps

Run through these after applying any fix:

• Hit Refresh Preview in Power Query Editor — the red error banner should be gone
• Check the type icons next to your key columns — both should show ABC (text) or 123 (number), not a mix
• Add a quick diagnostic step: = Table.RowCount(lookupTable) to confirm the table loaded rows at all (zero rows is a common gotcha)
• Close & Load, then compare the output row count against what you expected
• Used try...otherwise null? Add a filter on the result column to isolate null rows — those are your unmatched keys worth investigating

Quick Diagnostic Checklist

• [ ] Same data type on both sides of the key comparison?
• [ ] Any leading or trailing spaces lurking in key values?
• [ ] Did the source data change after the query was originally built?
• [ ] Is the lookup table loading zero rows?
• [ ] Using Inner Join when Left Outer is what you actually need?
• [ ] Key column name spelled and capitalized correctly in M code?