Medium severityquery
Power BI Refresh Error:
The key didn't match any rows in the table
What does this error mean?
A lookup or merge operation in Power Query tried to find a key value that does not exist in the lookup table. This happens when a join expects every key from the left table to exist in the right table.
Common causes
- 1A Table.SelectRows or lookup formula uses a value that is absent from the target table
- 2A merge with JoinKind.Inner returns zero rows and downstream steps fail on the empty result
- 3Reference data (dimension table) was updated and some keys that existed before no longer exist
- 4A parameter value passed to a function does not match any row in the lookup
- 5A Table.Column() or Record.Field() call references a field that does not exist in all rows
How to fix it
- 1Add error handling in Power Query: use Table.AddColumn with try...otherwise to handle missing key lookups
- 2Change the merge join type from Inner to Left Outer to return all rows from the main table even without a match
- 3Add a validation step that checks for unmatched keys and logs them rather than crashing the entire refresh
- 4Review the reference data table for deleted keys that existing fact rows still reference
- 5Use List.Contains() before attempting the lookup to conditionally handle the missing key case