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

36 REPLIES 36

@lemontree 

Just shooting from the hip, what I would do is to create a VBA script or similar, that opens each file and renames the sheets according to standard, you can use regular expressions for that e.g.

Dim Regex As New RegExp

Dim NewName As String, OldName As String

Regex.Pattern = "[\(\)]"
NewName = Regex.RE(OldName, "")

 

will remove all parantheses from name

hangtenboy
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

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

Anonymous
Not applicable

Thanks @mjc099 . I had the same problem when adding a new file to a folder data source. I changed the sheet/tab name in the new file to match sheet/tab name in the original spreadsheet file used to create data model, and the error cleared.

 

It would be great to have this error fixed in the future and purhaps use indexes to call for specific sheets in the future. As in my case, some automated repors saved in one folder will not have the same sheet names. For example, the reports saved in my folder will have sheet names tied to the date range they were generated for, hence the names will be different in each report.

Hi

In order to avoid the error ralated to the sheet name chnaging unexpectedly, i use the { recordnumber} notation instead , to access the Binary of the Excel File and later the Sheet. Here i access the first file ie {0} of my Sharepoint folder , then get its content and then take its first {0} sheet

 #"_FileContent" = try #"Filtered Rows"{0}[Content] otherwise error "Pas possible d'accéder aux données du fichier: " & _FileName,
#"Imported Excel" = Excel.Workbook(#"_FileContent"),
Feuil1_Sheet = #"Imported Excel"{0}[Data]

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

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?

I followed some of the steps above but came across with interesting finding. I got this error as well, but in my scenario it appeared this way:
With my own credentials, as sharepoint site owner, I was able to read the file(s) with no issues.
When changing the credential to service account this error started to appear. The error didnt come out from sharepoint that had standard members/visitors, but in sharepoint where members and visitors were managed through AD groups. I added the service account to the AD admin group which was part of site visitor, but it didnt work. After adding the service account directly to the sharepoints visitor member list the file started to load with no issues. Hope this helps someone else as well.

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Kudoed Authors