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 

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

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

[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

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

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

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

[MansoorSadat]

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

[lemontree]

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!