cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Advocate III
Advocate III

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

 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

24 REPLIES 24
Highlighted
Community Champion
Community Champion

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

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)
Highlighted
Advocate III
Advocate III

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

 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

Highlighted
Microsoft
Microsoft

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

Highlighted
Advocate I
Advocate I

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

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
Highlighted
Helper I
Helper I

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

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.

Highlighted
Regular Visitor

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

 ensure all tabs in worksheet are named EXACTLY as in previous updates. 

 

I received the same message when updated a datasource after three months of successfully updating same.  Expression Error: The key didn't match any rows in the table.

 

My data sourse is a monthly updated xlsx file.  My issue was resolved by ensuring all tabs in worksheet were named EXACTLY as in previous updates.   One of the tabs in the data had an underscore added.  the underscore did not exisit in previous updates. updated the spreadsheet tab resolved the keky didnt match message.

Highlighted
Regular Visitor

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

As an addition to the solution JMDH has already submitted, I have found the need, at times, to go into the Advanced Editor and change the name for the sheet to whatever it is in the file. So you changed the Sheet name to reflect what it used to be in the original but you still get the error? Check here:2018-05-24_9-08-45.jpg

Highlighted
New Member

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

I had a report connected to a folder and every month used to add new excel files in that folder to update the report. This happened when a new TAB in my new excel workbook had a different name than the old files connected to the report. So I just renamed the new tab in the new excel workbook to match the old tab in the old file excel files and it worked.

 

Hope it helps

Mansoor

Highlighted
Helper I
Helper I

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

Faced a similar problem where the sheet name changed and had to go and rename it but I'd like to have a solution that this is done automatically and not manually as my files are daily and i am not the one producing the source file which means that if not automated every day i need to open the file, rename the sheet, save and then update the query / report.

 

Any ideas on how to automate the process of a repeating incorrect sheet name to match the sheet name of the old series of source files ie...let's assume that all the daily files from Jan 1, 2018 till 30 May 2018 have a sheet named "Country (XYZ)" and from 31 May and onwards this same sheet is named "Country XYZ".

 

Thx in advance for your help!

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Kudoed Authors