Expression.Error: The key didn't match any rows in the table
By Tahir Nas · Examples run in our simulator · Published · Updated
Expression.Error: The key didn't match any rows in the table.
A lookup like Source{[Name="Sheet1"]}[Data] found no matching row. Often a sheet, table or file was renamed in the source.
Common causes
- A workbook sheet or table was renamed.
- Row lookup by a value that isn't present.
- Different capitalisation or trailing spaces in the key.
How to fix it
- Check the navigation step's key against the current source.
- Use Table.SelectRows plus a check for empty results when the key may be absent.
- Use try … otherwise to supply a default table.
Find the step that fails
- Open the query in the Power Query editor (Excel: Data → Queries & Connections → Edit; Power BI Desktop: Transform data).
- Click the steps in Applied Steps from the top. The first step that shows the error is where it starts; later steps only repeat it.
- Read that step's formula in the formula bar (View → Formula Bar if it's hidden) and compare it with the columns and values the previous step produced.
Minimal example
Raises the error:
#table({"Name", "Data"}, {{"Sheet1", 1}}){[Name = "Sheet2"]}[Data]Fixed:
#table({"Name", "Data"}, {{"Sheet1", 1}}){[Name = "Sheet1"]}[Data]Both snippets are run by the Power Query Exercises simulator on every build: the first must raise Expression.Error, the second must run.
Practise avoiding it
- beginner~7 minFree
Fix a query whose steps point at the wrong step (kite shop)
A step was renamed and the next step still refers to the old name. Repair the chain so the query runs.
- beginner~6 minFree
Remove a leftover step that cuts the data short (kite shop)
A Kept First Rows step left over from testing silently drops orders. Find it and remove it.
- beginner~5 minFree
Return the last step, not an earlier one (kite shop)
The query builds a Line Total column, but the result doesn't show it: the `in` clause returns an earlier step.
- beginner~6 minFree
Fix a Changed Type step that breaks two columns (kite shop)
An automatic type step turned customer names into errors and stripped the zeros from order numbers.