cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mpaczkowski
Regular Visitor

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

I tried to combine 3 excel files from folder with power bi query, i make some transormation (eg. change type value), during loading proccess i recive message Expression.Error: failed to save changes to the server. error returned ole db or odbc error, Expression error, The key didn't match any rows in the table,

 

any suggestions how to solve this issue?

 

best regards

Mariusz

1 ACCEPTED SOLUTION

it works, i was issue of data format there shoudnt be difference between excel and power bi (query editor), i setup data format for every column  (whre should be text i setup in power bi query  editor "text", where should bu number i setup "number") and its helps

 

thx for help

View solution in original post

15 REPLIES 15
Prometheus2183
New Member

I had the same issue with a different solution someone might find useful. I was bringing in about 20 files from a folder and using combine. The error OLE DB ODBC code 0x80040E4E came up for me due to one of the worksheets in one of the workbooks name differing to the rest. Once I renamed and set up the query again it worked absolutely fine.

 

Another interesting point after a bit of research was finding that relationships can cause this error, too. Just wasn't the case for me and wasn't mentioned here.

GilbertQ
Super User II
Super User II

Hi @mpaczkowski

 

Are you using the latest version of the Power BI Desktop?

 

And when you get the expression error, is this in the Query Editor or when you are loading the data into the Power BI Model?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

i downloaded power bi (32- bit) few days ago

during loading data

In my case, the "Expression Error was due to a mismatch in the Excel file name and sheet name. I extracted the data in excel format manually from the SQL database and tried to refresh the data in Power BI desktop. "Make sure the Excel file name, table name, and format are the same as the initial data format and name that you used while creating your Power BI reports"

Hi there. I would suggest that you make sure that each column has got the right data type defined in the Query Editor




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

it didnt help, any other suggestions?

Hi can you uninstall and then make sure you delete all power bi folders.

And then reinstall.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

no results

it works, i was issue of data format there shoudnt be difference between excel and power bi (query editor), i setup data format for every column  (whre should be text i setup in power bi query  editor "text", where should bu number i setup "number") and its helps

 

thx for help

View solution in original post

Hello,

 

I am getting the same error when cobining files from sharepoint using combine query. Could you please expalain more on what need to be done ? I need to complete a business critical activity and having this issue!

 

@Bharat_CV 

 

Are you using those files data for the first time ? or you are trying to replace  the files in your power bi desktop or server with refreshed data?

 

If you are trying to replace the existing files in sharepoint with new files , Please make sure the file names, sheet name, data format of the new files are same as old files. Otherwise PowerBI throws expression error.

 

For Ex- if your old file name was "ABC" and new file name is "ABCD" and you are trying to replace "ABC" file with "ABCD" power bi throws an error.

 

Please also check the name and  format of your columns in your file. If "Text" data type is mentioned as "Number" data type or vice versa, Power Bi throws an eror.

 

Please use the same names, format in your combine query as well.

 

 

Hello @Aswini_C_S  thank you for the response.

 

I am using the excel files for the first time.  I have multiple excel files with different name and have 2 sheets in each file with. I need to consolidate the first sheet in each excel,the number of columns and cloumn name is same.

For Example: First excel  file name : March 1st week which has 2 worksheets, sheet 1 name- 'March 1st week' and the other sheet with the default name 'Sheet2' same goes for second excel.

 

When using combine query I am selecting the first sheet, which I need to combine for all excel files.

 

As a workaround, I deleted the Sheet 2 and renamed the first sheet name to default name to 'Sheet1'. This is working fine and the exact output what I intended. However I would still need sheet 2 as well with the different sheet name I am still trying to figure out.

 

Appreciate your support

Thanks and Regards,

 

I am assuming that you wanna combine sheet 2 data of all the files and all files sheet2 column names, format are same.

 

Can the default name for all  files sheet2  be same?

 

If you can give same name for all files sheet2, you can rename sheet2 with same different sheet name in your sharepoint files and you can use it.

 

 

Please find below you tube  video link for more details.

 

https://www.youtube.com/watch?v=GvKYUgka_4Y

 

Thank you!

 

Hi @mpaczkowski,

Congratulations, you have resolved your issue. Please mark your solution as workaround, so that more people will benefit from it.

Best Regards,
Angelia

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors