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

 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
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

Microsoft
Microsoft
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
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.

Regular Visitor

 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.

Regular Visitor

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

New Member

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

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
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors