Power QueryExercises

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

How to fix it

  1. Check the navigation step's key against the current source.
  2. Use Table.SelectRows plus a check for empty results when the key may be absent.
  3. Use try … otherwise to supply a default table.

Find the step that fails

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

Guides

Related lessons