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 

[Anonymous]

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.

[ksimpson50502]

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? 

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

[Alberto_K]

Hi

don't know if anyone else got my same case for this error but basically a colleague of mine changed the name of the sheet in the master data source.

It was not a table but a simple range so no need to convert the range into a table, just the sheet's name to reset 

[JenDenning]

I received this same error when my data source was a folder. i had 6 files i was referencing and all of the files had the same sheet and table names. 

To fix i converted the table to range and reset the table. 

No clue why it worked, but it did. 

[US285126]

How do I have the query skip files which don't have the targeted sheet name in them? It seems like the query is just stopping when it reaches a workbook without the targeted sheet name.  I'd like it to continue to the rest of the workbooks.

[yoyo_var]

Hi, I am getting same error with Big query data set. The same sql is not showing error in another user power BI. Any idea how to fix it?

[omikeyz]

I had this today when I was trying to get data in an MS Access db but the table was actaully a linked file to an Excel sheet. I changed it to a "table" and then it worked.

[roy_cal]

for my particular issue it was trying to combine multiple files from the same folder but the sheets had different names (named the same as the file)

as per some of the solutions above I changed the Source sheet name from a specific name to the sheet number, all was good

orginal:

Source = Excel.Workbook(Parameter1, null, true),
Receipt_1 = Source{[Name="Req_84521"]}[Data]

fixed:

Source = Excel.Workbook(Parameter1, null, true),
Receipt_1 = Source{0}[Data],

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

[Anonymous]

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.