If you get an Expression.Error: The key didn’t match any rows in the table in Power BI, it means that you’ve made some changes to your source file and haven’t updated them in the Power Query.
In most cases, the error arises when the Sheet’s Name is not the same on your original and destination file. Apart from this, the following causes could also be responsible for the error.
- Deleted Sheet or Column in Source File
- Renamed Column Header/Table
- Set Privacy Settings in Permission
Regardless of the cause, in this article, I’ll walk you through these tried and tested fixes to solve the error.
Refresh Query
Firstly, manually Refresh the Query on your Power BI. There are chances that the changes you’ve made on the original data are not updated on the loaded data. For example, added columns/rows, deleted columns, new worksheet, etc.
On your Power BI desktop, navigate to the Home Tab. Then, from the Queries section, click on Refresh. This should troubleshoot the error.
Rename Excel Sheet/Columns/Table Names
Next, you’ll get “The key didn’t match any rows in the table” when the Power Query cannot connect or establish a connection with the data source. This can happen when the Sheet Name, Column headers, or Table names of imported data do not match with the original sheet.
For example, maybe you renamed “Sheet3” into “SalesDashboard” in your local workbook. But, there’s still “Sheet3” in the Power BI.
So, check and rename the headings to match with the source file. Most users reported this fix resolved their error.
As an example, I will change the Sheet Name using the Advanced Editor.
- On your Power BI Desktop, head to the Home Tab.
- From the Queries, click on Transform Data.
- Now, on Power Query Editor window, select Sheet3. Click the Home Tab. In the Query group, choose Advanced Editor.
- On the Advanced Editor Window, enter Sheet name on
=Source{Item=""}Here, I will replace the name with “SalesDashboard.” Then, click Done.
- Click on Close & Apply.
Delete from Model
Did you delete an Excel Sheet from your main file?
When you delete a sheet that has been loaded in the Power BI, the Power Query cannot access that sheet anymore. Thus, you’ll encounter “The key didn’t match any rows in the table.”
Whatever changes you make in the Source workbook, you must update them in the Power BI loaded data too. So, clearing that removed worksheet should solve the error.
Hover over the Data pane on the right side. Right-click on the Sheet and choose Remove from Model. Pick Yes to confirm.
Then, click the Refresh menu in the Home tab. The error will no longer appear.
Remove Navigation
Some users who imported the database directly from the SQL server without creating the table also encountered “The key didn’t match any rows in the table.” So, another fix to solve it is to delete the Navigation step on your Power Query.
- From the Home Tab, click on Transform data.
- On the Power Query Editor window, hover over the Applied Steps panel. Delete Navigation.
- Click Yes. Then, Close the Power Query Editor.
Clear Permissions
If you’ve imported the external data source shared by others, it is possible that the user has set Permission settings to protect the workbook.
Eventually, when you attempt to refresh the Query, Power Query cannot update the connection or combine data which results in an error.
In that case, you can clear the Permissions.
- On your Home Tab, click on Transform Data > Data source settings.
- Click on Clear Permissions.
- Hit Delete. Then, close the window.
