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

[Chapin4u]

Hi, 

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

[altomost]

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

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:

[mjc099]

 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]

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.

[jmdh]

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]

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]

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

[mh30312]

This worked! Thank you 😊

[rodrigolucas]

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!

[ggranger]

Bug.

[Anonymous]

Those steps really fix it, thanks

[v-sihou-msft]

@Chapin4u

When does this error occur? Can you click on source  step and "refresh preview" ?

See similar threads below;

https://community.powerbi.com/t5/Desktop/Expression-Error-The-key-didn-t-match-any-rows-in-the-table...

http://community.powerbi.com/t5/Integrations-with-Files-and/Expression-Error-The-key-did-not-match-a...

https://community.powerbi.com/t5/Service/Key-Expression-Error-The-key-didn-t-match-any-rows-in-the-t...

Regards,

[MarcelBeug]

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.

[jmdh · 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...

[HLST]

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?

[veehoo]

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.