Get data from folder vs. missing worksheet / Expression.Error: The key didn't match any rows in the

[Anonymous]

Hi!

I’m having issue with getting data from folders and maybe anyone having the same issue can help to resolve it.

My database is based on getting data from folder. Every month a new excel file is inserted into this folder.

In excel files there are usually two worksheets: IMPORT and EXPORT.

However there are months where there is no data for EXPORT, therefore the worksheet EXPORT is not being created (no need to have empty worksheet). My query for EXPORT is based on “EXPORT” worksheet name. Whereas I do have data for IMPORT every month, I do get an error for lacking of EXPORT worksheet for months, when there is no data.

Error:

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

I tried to use Remove Rows -> Remove Errors function, however without any success - the rows are not deleted and error still appears. I also tried to put filter on any column to exclude “Errors” but these are not visible on the list.

After creating empty “EXPORT” worksheets, the error disappears, however this is not a solution I would like to follow. Reports are external and I would like to avoid manual creation of empty worksheets.

Is there any way to solve this issue? 

For empty months I would like to see only “nulls” when visualizing data.

Currently the database is on my desktop (not sharepoint), connected to OneDrive.

Database structure below:

Folder: ES_EXIM_XPO

-> Xls file: ES_EXIM_XPO_2018_11.xlsx -> EXPORT worksheet + IMPORT worksheet

-> Xls file: ES_EXIM_XPO_2019_07.xlsx -> only IMPORT worksheet

-> Xls file: ES_EXIM_XPO_2018_08.xlsx -> only IMPORT worksheet

-> Xls file: ES_EXIM_XPO_2018_09.xlsx -> only IMPORT worksheet

After creating empty “EXPORT” worksheets in all files, the error disappears, however this solution is not an option.

[v-juanli-msft]

Hi @Anonymous 

Some workarounds you could check:

https://systemmanagement.ro/2018/05/18/power-bi-the-key-didnt-match-any-rows-in-the-table/

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

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Juanli,

Thanks a lot for your reply!

I tried to follow instructions from the first link, however there is no "Go to Error" button I can find.

How to access it? Is there any other way to change these settings?