Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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.
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?
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".
it didnt help, any other suggestions?
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
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!
@Anonymous
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 @Anonymous 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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |