Power Query Problems

Fix Power Query Error: The Key Didn't Match Any Rows in the Table in Excel

Maira Mehtab Sep 20, 2026 868 views

Question details

The user needs to resolve an intermittent Power Query error stating 'The key didn't match any rows in the table' when refreshing SharePoint-hosted databases in the desktop app.

Product: Microsoft Excel (Power Query)
Device & OS: not provided
Scenario: Refreshing two SharePoint-hosted Excel databases using Power Query in the Excel desktop application.
Observed behavior: The desktop application intermittently fails to refresh the queries and displays a key mismatch error, even though it refreshes successfully in Excel for the Web and persists after clearing the cache and reinstalling Office.

Before you start

Ensure you have a stable internet connection to SharePoint and sufficient permissions to access the hosted Excel files before modifying any query parameters.

Solution 1Recommended

Verify Source File, Table, and Worksheet Names

Check if any structural changes in the SharePoint files or data sources are causing the query reference to break.

Power Query relies on exact matches for file paths, sheet names, and table names. If a user modifies or renames a table in the source SharePoint file, the desktop query will fail to find the specific 'key' it was programmed to locate.

Open Power Query Editor

In Excel, navigate to the 'Data' tab on the ribbon, click 'Get Data', and select 'Launch Power Query Editor'.

Check Applied Steps

Select your problematic query from the left pane. On the right side, go to the 'Applied Steps' pane and click on the 'Source' or 'Navigation' step.

Inspect the Formula Bar

Check the formula bar at the top to see the exact file path, sheet name (Item), and table name (Kind) being referenced.

Validate Source Data

Open the source file in SharePoint and confirm that the referenced file name, worksheet name, and table name match exactly, including casing.

Case Sensitivity: Power Query is strictly case-sensitive. Ensure that table and sheet names match exactly, including capitalization and trailing spaces.

Solution 2

Isolate the Failing Query Step

Step through the Power Query process to identify exactly where the key mismatch occurs.

Free Microsoft Office alternative

Try WPS Office for Seamless Data Management

If you frequently encounter complex Excel Power Query errors or intermittent refresh issues, consider switching to WPS Office. It provides a lightweight, highly compatible, and user-friendly alternative for handling your spreadsheets and daily data analysis.

1. Download and Install: Visit the official WPS website to download and install WPS Office for free.
2. Open Your Spreadsheet: Launch WPS Spreadsheet and open your existing .xlsx files directly without any conversion.
3. Process Data Smoothly: Enjoy stable data editing and analysis with full format compatibility.

Free and lightweight spreadsheet applicationExcellent compatibility with Microsoft Excel (.xlsx) formatsFamiliar user interface for a smooth transitionStable performance for everyday data processing tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why does the Power Query refresh work on Excel for the Web but fail on the desktop app?

Excel for the Web and the desktop application use different query execution engines and caching mechanisms. The desktop app might hold onto an outdated metadata cache, or there could be version-specific differences in how the desktop Power Query engine authenticates and connects to SharePoint.

Does clearing the cache fix Power Query key mismatch errors?

While clearing the cache (Data > Get Data > Query Options > Data Load > Clear Cache) can resolve some stale data issues, it typically does not fix structural mismatch errors where a table, column, or sheet name has actually been changed in the source file.

How do I find which table is causing the key mismatch error?

Open the Power Query Editor, select the query showing the error, and examine the 'Applied Steps' pane. Click through each step sequentially until the error message appears on the screen; the error originates in the step you just clicked.

Excel & Data Problems