cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Chapin4u
Microsoft
Microsoft

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

2017-04-07_15-19-41.png

 

Hi, 

I need help to understand this error message (above image) and how to fix it, thanks 

1 ACCEPTED SOLUTION

 A typical case would be when you connect to an Excel file and then your Query attemps to access a Sheet that does not exist in the Spreadsheet...

View solution in original post

30 REPLIES 30
Jayadev
Helper I
Helper I

I am also facing similar issue in PowerQuery (Excel 2013).   I have created a database in Cloud and connected with powerquery and created few reports.  I have taken a dump of the DB and imported in my local system.  Now I am trying to connect to localhost.  The schema & table names are same, still it is not working.  Very frustrating, tried many ways, please help.

altomost
Advocate I
Advocate I

you can make this error occur if you:

  1. connect to a database
  2. select any table and load it
  3. click home - edit queries
  4. click source under applied steps
  5. click advanced options in the popup
  6. type SQL statement e.g. select * from [yourtablename]
  7. click ok
  8. click close and apply
  9. You now see the error above (Expression.Error: The key didn't match any rows in the table)

Solution

  1. click home - edit queries
  2. under applied steps, remove navigation
  3. click close and apply
  4. The error is fixed

This worked! Thank you 😊

Appreciate your support, solved! But in my case, I had just changed the name of the sheet1 to another custom name, and that was the cause of the error. So I kept the sheet1 as it was 🙂 thanks!

Bug.

Anonymous
Not applicable

Those steps really fix it, thanks

MarcelBeug
Community Champion
Community Champion

You have a query in which a row is selected based on a key value and that value does not exist.

 

To fix it make sure the key value exist or adjust the code so it does not point to a non-existing key value.

 

Specializing in Power Query Formula Language (M)

 A typical case would be when you connect to an Excel file and then your Query attemps to access a Sheet that does not exist in the Spreadsheet...

View solution in original post

HLST
Frequent Visitor

I have renamed a spreadsheet in excel and received this error. I then renamed the dataset field on power bi desktop and tried to refresh, though I received the same error. Is there a way to fix this?

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.