Expression.Error: The key didn’t match any rows in the table.

In Power Query, Expression.Error: The key didn't match any rows in the table occurs when a navigation record lookup, such as {[Item="Sales", Kind="Sheet"]} or {[Name="Customers"]}, fails to find a matching entry in the navigation table. This typically happens because an Excel sheet, named range, SQL table, or file was renamed, deleted, or moved.

Quick Answer

Look at the formula bar of the broken step (often the second or third step, named #"Navigation" or #"Item"). Locate the lookup key inside the curly brackets: {[Item="..."]} or {[Name="..."]}. Compare that name to the actual names present in the source file or database. Correct the spelling, check letter casing, or reselect the object in the navigator window.

---

Anatomy of Power Query Navigation Keys

When you connect Power Query to an external container (like an Excel workbook, Access database, or SQL Server instance), the initial step returns a structural directory called a Navigation Table.

For an Excel file, this navigation table includes metadata columns such as Name, Data, Item, and Kind:

Name	Data	Item	Kind	Hidden
Sales2024	[Table]	Sales2024	Sheet	FALSE
LookupData	[Table]	LookupData	Sheet	FALSE

When you click on a sheet in the Power Query Navigator UI, Power Query automatically writes a row lookup record using curly brackets and square brackets:

#"Selected Sheet" = Source{[Item="Sales2024", Kind="Sheet"]}[Data]

This instruction tells the engine:

“Search the navigation table for the single row where Item equals 'Sales2024' and Kind equals 'Sheet', then extract the nested table in the Data column.”

If someone renames that worksheet in Excel to Sales_2024, the lookup fails completely. Because the search key Sales2024 no longer matches any row in the directory, Power Query stops and throws:

Expression.Error: The key didn't match any rows in the table.

---

Common Triggers and Step-by-Step Solutions

1. Renamed or Deleted Worksheets / Tables

This is the most common cause. A source workbook was updated, and a tab name changed slightly (e.g., "Orders " with an accidental trailing space vs "Orders").

• How to Fix:
  1. Click on the Source step (the step right above the failing navigation step).
  2. The data preview grid will display the available objects inside the source.
  3. Look down the Name or Item column to identify the exact current spelling of your target object.
  4. Select the failing step again.
  5. In the formula bar, edit the string inside [Item="..."] to match the exact name visible in the Source step.

2. Case Sensitivity Mismatches

In SQL databases, SharePoint lists, and M-code lookups, object names are case-sensitive.

• If your M-code specifies {[Name="inventory"]}, but the source system serves it as {[Name="Inventory"]}, the record lookup will not resolve.
• Update the M-code to match the database schema’s casing exactly.

3. Source File Swapped with Missing Tabs

If your Power Query workflow imports a file from a folder path that gets replaced weekly, the new file must contain the exact same sheet names or table headers as the original template.

• If one weekly file is missing the expected tab, the query will halt on refresh.
• If your file structure changes unpredictably, avoid hardcoded navigation keys. Instead, dynamically grab the first available sheet regardless of its name:

// Instead of this:
#"Selected Sheet" = Source{[Item="WeeklyReport", Kind="Sheet"]}[Data]

// Use positional zero-indexing:
#"Selected Sheet" = Source{0}[Data]

This adjustment instructs Power Query to read whatever data sits on the first sheet of the workbook, bypassing specific sheet naming dependencies entirely.

4. Excel Table vs Sheet Ambiguity (Kind Mismatch)

An Excel workbook can contain both a sheet named Accounts and an official Excel Table (ListObject) named Accounts.

The navigation step uses the Kind property to distinguish between them:

• Sheet reference: Source{[Item="Accounts", Kind="Sheet"]}[Data]
• Excel Table reference: Source{[Item="Accounts", Kind="Table"]}[Data]

If a user converted the raw sheet range into a formal Excel Table, or vice versa, the Kind tag changes. If Power Query looks for Kind="Table" but only finds Kind="Sheet", the key will not match. Ensure both the Item name and the Kind attribute match the current source object type.

If you suspect an Excel table was deleted or moved, see Fixing “Expression.Error: We couldn’t find the Excel Table ‘Table1’.” for targeted guidance on recovering missing ListObjects.

---

Verification and Best Practices

To confirm your fix:

1. Select the corrected navigation step.
2. Confirm the preview grid populates with your data columns instead of the error banner.
3. Click Refresh Preview on the Home tab to ensure the query processes the data source cleanly from beginning to end.

To prevent this error in long-term automated workflows, use named Excel Tables rather than worksheet tabs whenever possible, lock down the template workbook tabs against user renaming, or reference sheets positionally ({0}) when dealing with single-sheet exports from external systems.