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

This error occurs in Power Query when an M-code step attempts to navigate to an object (such as an Excel worksheet, table, column name, or database view) using a key record lookup, but that specific object no longer exists in the target source. Power Query returns this error because it cannot find an exact match for the unique name or identifier specified in the formula bar.

Fast Fix

Click the failing step (usually Navigation) in the Applied Steps pane. Look at the formula bar for the key declaration: {[Item="TargetName",Kind="Sheet"]}. The object in the underlying source was likely renamed, deleted, or had its letter casing changed. Update the string in quotes to match the new name of the sheet, table, or file exactly.

---

Diagnostic Snapshot

Property	Details
Error Type	Expression.Error
Exact Message	The key didn't match any rows in the table.
Common Step Location	Navigation, Source, or any step containing record lookup braces { [...] }
Primary Root Cause	Mismatched worksheet name, renamed SQL table, altered database schema, or modified SharePoint file path
Case Sensitivity	Strict. Power Query treats "Data" and "data" as completely different keys.

---

Understanding the Key Lookup Mechanism

To understand why this error happens, examine how Power Query reads container-style data sources like Excel workbooks, SQL databases, or folder structures.

When you connect to an Excel file, the Source step returns a navigation table that lists all objects inside that file:

Name	Data	Item	Kind	Hidden
Sheet1	[Table]	Sheet1	Sheet	FALSE
RawData	[Table]	RawData	Table	FALSE

When you click on Sheet1 in the user interface, Power Query generates an M-code navigation step that looks like this:

Source{[Item="Sheet1",Kind="Sheet"]}[Data]

The curly brackets containing square brackets {[ ...] } represent a record lookup (a key). Power Query searches the navigation table for a row where the column Item equals "Sheet1" and Kind equals "Sheet".

If someone renames the tab in the source workbook from Sheet1 to Sheet_1, or deletes the tab entirely, the lookup key finds zero matching rows. Because Power Query requires an exact single-row match to return the [Data] column, the mashup engine stops and displays: The key didn't match any rows in the table.

---

Step-by-Step Diagnostic & Fix

Step 1: Pinpoint the Failing Key

1. Open the Power Query Editor.
2. In the Applied Steps pane, click directly on the step showing the yellow exclamation mark or error icon.
3. Look at the Formula Bar at the top of the window.
4. Locate the text inside the lookup brackets {[ ... ]}:
   In this example, Power Query is looking for a table named CustomerOrders in the dbo schema. = Source{[Schema="dbo",Item="CustomerOrders"]}[Data]

---

Step 2: Inspect the Available Source Objects

To see what objects actually exist in the source:

1. Click on the applied step immediately above the failing step (typically the Source step).
2. The main window will display the master navigation table showing all available items currently returned by the connection.
3. Check the Name or Item column:
   • Has the worksheet or table name changed?
   • Has a space been added (e.g., "Sheet1 " instead of "Sheet1")?
   • Has the casing changed (e.g., "sales" instead of "Sales")?

Actual Table Contents:
---------------------------------------------
| Item          | Kind   | Hidden           |
|---------------|--------|------------------|
| Customer_Data | Sheet  | FALSE            |  <-- The sheet was renamed!
---------------------------------------------

What your M-Code is searching for:
{[Item="CustomerOrders", Kind="Sheet"]}     <-- Does not match any row!

---

Step 3: Correct the Step

Method A: Edit the Formula Bar Directly
If the object was renamed, update the formula to match the new name:

1. Select the failing step.
2. In the formula bar, change the string inside the quotes to the current object name: // Change from: = Source{[Item="CustomerOrders",Kind="Sheet"]}[Data] // To: = Source{[Item="Customer_Data",Kind="Sheet"]}[Data]
3. Press Enter. The step will evaluate successfully.

Method B: Re-navigate Through the UI
If multiple properties changed:

1. In the Applied Steps list, right-click and Delete the failing step (and any subsequent broken navigation steps).
2. Click the gear icon or the navigation icon on the Source step.
3. Pick the intended sheet or table from the updated list of objects.

---

Common Variations of This Error

1. Folder or SharePoint Path Renaming

When querying files from local directories, network shares, or SharePoint folders, navigation often looks up a file by its Name property:

Source{[Name="Budget_2026.xlsx"]}[Content]

If the file name is updated to Budget_2026_v2.xlsx, the query breaks with the same error.

Defensive Solution:
Instead of hardcoding the filename in a key lookup, use filtering:

1. Connect to the folder.
2. Filter the Name column using Text Filters > Begins With ("Budget_2026").
3. Sort descending by Date modified to keep the newest version.
4. Click the row selector or combine binaries.

For related SharePoint document library connection and navigation failures, see Troubleshooting SharePoint Folder Connection Failures in Power Query.

2. Position-Based Indexing vs. Key Lookups

If a source sheet name changes every month (e.g., "Jan 2026", "Feb 2026"), key lookups by name will always break. You can switch to a zero-based row index lookup if the target sheet is always the first tab in the workbook:

// Replace name-based key:
= Source{[Item="Jan 2026",Kind="Sheet"]}[Data]

// With positional index (first row in the table):
= Source{0}[Data]

Warning: Only use positional indexing {0} if the physical layout of the source file is strictly controlled. If someone inserts a new first sheet, the query will read the wrong data without warning.

If the error persists but references a column header name rather than an object key, refer to Power Query “Expression.Error: The column of the table wasn’t found”. If variable names inside the M-code script itself are broken, see How to Fix “Step references an unknown variable” in M-Code.