Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Chapin4u
Employee
Employee

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

48 REPLIES 48
rossparker
New Member

I cannot get past this error.  It is when trying to update a Data Source for File Folder of CSV files.

First thought : i believe that Folder access works only for Excel files (xlsx not CSV extension)

If you send a link / Pbix i can have a look

Changing them to XLSX did not work either  😞

 

I just redid the query from the folder to take care of it.

Chris99
Advocate III
Advocate III

@altomost - Thank you very much, that was exactly what I did and removing the Navigation fixed it.

 

It is a shame you can't import views from SQL Server, instead you have to import a table and then edit the query to act like a view. That was when I hit this error.

 

Chris

Anonymous
Not applicable

If you are using Tables/Views from SQL Server, check whether the power bi credentials have the permission to read from the table/view you have linked.

1. Go to "Options and settings" -> "Data source settings" -> "Edit Permissions" 

2. Capture.PNG

3. Cross check in the database whether this user role has the permission to access the table/view.

4. Go to SQL server

5. Right click on the table/view. Click "Poperties" ->Go to "Permissions".

6. Capture2.PNG

7. Add the user role/ give permission accordingly.

8. Go to Power BI and refresh.

Anonymous
Not applicable

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!

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

Anonymous
Not applicable

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)

Hello, I am getting this error and I have hundreds of files that are being joined from a folder. I am unable to pinpoint where the error is coming from. I have looked at every file that has been updated since my last data refresh and I am failing to find the error. Does anyone know how to understand where the error is occuring? Like what file has the value that doesn't exist? 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.