Error returned: 'OLE DB or ODBC error: [Expression Error] The key didn't match any rows in the table

[Anonymous]

Downloaded to latest Power BI Desktop: When uploading new data in Power Query, I am now receiving the following error message which I was not receving prior to upgrading.

Query1 Failed to save modifications to the server. Error returned: 'OLE DB or ODBC error: [Expression Error] The key didn't match any rows in the table...'

Any idea on how to fix this one...Thanks

[ramdasanudas]

Try to clean the data load cache: File -> Options and Settings -> Options -> Data Load -> Clear cache and Save the file and Load again

[nitinrungta]

Hello all,
Here is my two cents on it and how I fixed it. 

@Vibration85 

Go in the source(power query) and fix it out there. Remove(filter out) all other useless files in the first few steps of the source itself. Because some new sheets in Sharepoints may have errors and if it is already not being filtered out, then it will stop the process and you may never understand why.

@ksimpson50502 - if you have hundred files, are they all in the same folder. Irrespective of the sharepoint problem, you have to have all similar sheets for the automation to break. If you have hundred sheets, you should probably maintain different folders for the similar sheets. Similar sheets have similar properties, like following same sheet name and different dates for example. It is very easy to apply a rule in power query and filter it out. If you are able to filter out the whole unneccesary sheets in the Source without "Expanding",(ie, appending all sheets), then you should not face any errors.

@CCMK80 c I think we tried the same solution and yes, I do feel the pain. If they can just make it clear what exactly is the error.

[CreativeSteph]

I experiened this error when setting up a folder pull in power query editor and the fix was to make all the sheet names match in the each Excel workbook file.

[Vibration85]

I faced the same issue last week, any successes? 

[ksimpson50502]

I haven't found a way to make this error message useful, as it usually just tells you there is a problem but not what the problem really is. I have many files sourced in my query, so if I notice one time it doesn't refresh properly, I go back and check the files that have been modified since the last refresh. I check to make sure all columns and rows are unhidden, have the same naming convention and order, and the worksheet names in the workbook are all the same. 

[Vibration85]

I have confirmed that I do not have many tables or files, yet I encountered an error indicating limited memory usage. Upon checking my workspace memory, I noticed it shows a large amount of memory. However, I believe this error may be referring to memory per query execution, which I am unsure how to check

[LSeat2022]

I spent hours troubleshooting this from all the suggestions. I was doing get data from folder. All files were excel. It would read the first dataset and error on the others. The end result is that I had to make all the files have the same tab name. Frustrating that the tab name in the workbook was even an issue. 

[mgallipeau]

This took care of my issue. Would have never imagined that to be the problem.

[paul0907]

If every excel has the data on the same sheet you can fix it easily using transform. in the example below it's the first (0) sheet

let
Source= Excel.Workbook(Parameter2, null, true),
Sheet1_Sheet = Source{[Item=Source{0}[Item],Kind="Sheet"]}[Data],

[V-pazhen-msft · Accepted solution]

@Anonymous 

A typical case would be when you connect to an datasource and your Query attempts to access a table or column that does not exist in your datasource. Check if there is any table/column names are not matching.

Reference:

Chris Webb's BI Blog: Understanding The “The key didn’t match any rows in the table” Error In Power Query In Power BI Or Excel Chris Webb's BI Blog (crossjoin.co.uk)

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[sybon]

I encountered a similar issue in my PowerBI reports where I discovered that the sample file had a sheet named 'data,' whereas the latest file had a sheet named 'DATA.' Later I changed the sheet's name from "DATA" to "Data" and I got my result.

[ksimpson50502]

What is the best/fastest way to do this? I have hundreds of files in a folder I am combining in Power BI and this is like looking for a needle in a haystack...I have checked every file that has been updated since the last refresh, and I cannot find any discrepancies. 

[Timur_Kurenkov]

I am getting the same message error: OLE DB or ODBC error: [Expression.Error] The key didn't match any rows in the table..

But I have data being sourced from hundreds of files, filtered out by file name and tab name parrameters. Is there any way I can figure out in which excact file an error might possibly be?

[ksimpson50502]

did you ever figure this out or get an answer? i have the same issue. it tells me i have an error but not what or where it is. 

[CCMK80]

In my case, I was pulling data from a folder containing multiple Excel files.  The issue was that my query was looking for a 'table' within the Excel file called Table1, and in a couple of cases the Excel table had a different name.  It would be helpful though if PBI at least told you which file it had failed on!