Forum Discussion
Data refresh error: OLE DB or ODBC error: Exception from HRESULT: 0x80040E1D.
I am trying to refresh a table (with less than 50 records and 200 columns). The source is a text file on my desktop and I am pretty sure nothing has changed except a few additional records in the new file.
The error message says: OLE DB or ODBC error: Exception from HRESULT: 0x80040E1D. This happened in the old version of the Desktop as well as the new version (i.e, latest version that I downloaded and installed today).
Has anyone seen this before? Can I get some tips on troubleshooting this error? Thank you.
48 Replies
I was also able to fix this error by updating the data type of the different columns. I am using as source an Excel file with 32 columns, a mix of text, number, date and percentage data. After correcting the data type of each column in the query editor, this OLE DB/ODBC error was solved.
Was experiencing the same issue: Set excel file as data source, added columns to file, tried to refresh, recieived "OLE DB or ODBC error: Exception from HRESULT: 0x80040E1D" error.
Identified Issue: Script for promoted headers is not updating to include headers of new columns
Solved by: Opening Edit Queries, deleteing promoted headers from applied steps, refreshing, slecting "use first row as headers"
If you go line by line and update each source individually, instead of using the refresh button in the toolbar you can find the specific error in the specific table that is causing the problem. For some reason this error causes the entire chain to break and prevents the data from updating. I discovered a blank row had been entered in one of my tables. Because this table had relationships associated with that row, it broke everything. I love Power BI, but the error messages are criptic and extremely unhelpful.
I had this same issue. I was able to fix it by adjusting my column data types prior to applying the query changes. My dates were refreshed as text. Once I changed them to Datetime the issue was resolved.
I just faced the same issue and fixed it by changing the format of column crating it. Here is the story:
I connected a table which is sitting down on the cloud. One of my column which is actually a date was exported as “Whole number”. I did my report and ran several refreshes as new entries were coming. So far everything was working well. After the third refresh, I realized that I needed to add a new chart in my report using the date column. I changed the format form "whole number" to "DateTime" type. And when I did the fifth refresh, this is where I got this error message. When I put it back to “whole number” format, I was able to refresh and did not get an error message.naeljb Thank you!! This worked for me as well. It was driving me nuts all day. Thank you!!!!!
Hi!
I have been having the same issue today and was able to resolve it.My issue was that in the Excel, some of the format were different for the date. I have a column Month and up to June, it was written as "6/1/2017" and for July, it was written as "July-17".
So I made sure all the my columns were formatted the same way in Excel, then saved the file as a CSV (to clear all Excel formatting), converted back into Excel and then refreshed in Power BI. And it worked! Hope it helps.
I had this same issue. I was able to fix it by adjusting my column data types prior to applying the query changes. My dates were refreshed as number. Once I changed them to Date data type the issue was resolved.
Hi InsureBI,
Do you refresh data in Data View or Report View of Power BI Desktop? I am not able to reproduce this error when imitating your scenario. Could you please share me your text file so that I can test it in my Power BI Desktop?
In addition, check that if the connection string is changed in Advanced Editor of Query Editor after refreshing data. Change text file path, re-add the connection string in Advanced Editor and refresh data and check if the error goes away.
Thanks,
Lydia ZhangTry refreshing the queries one by one and you will see the actual error for the problem query. In my case it was a bad relationship that had been built (one to many). I deleted the bad relationship and everything worked fine.
Hi all,
I found this video that might be helpful. I could solve the issue thanks to it. You just need to check if the data type of the columns is correct. They should also match the data type you have in your DataBase.
OLE DB or ODBC error: Exception from HRESULT: 0x80040E1D | Power bi Refresh Error - YouTube
Thank you so much - this was driving me crazy! It just suddenly stopped working but this did the trick 😊
I received a similar error upon refresh of my Power BI dataset when it was working fine in the past.
I found a workaround as follows:
1. Use the Transform Data option from the Power BI desktop Home menu.
2. Click on your query that is producing the OLE DB/ODBC Error and select the Advanced Editor menu option.
3. Save a copy of your original logic that appears in the Advance Editor by copy/paste to Wordpad.
4. Comment out or remove a large portion of your query logic so that you leave a cutback version and click "Done" from within Advanced Editor to save the revisions.
5. Exit the Transform Data area by choosing the "Close and Apply" option.
6. Your query will attempt to reload for the data refresh. Determine if it is successful. If not then you need to keep commenting out the logic in it until you can get it to run successfully.
7. Once you get the query to run successfully for your data refresh, go back into the "Transform Data" area and the Advanced Editor option and add back into the query the original logic, save and exit with the "Close and Apply"
8. After performing the above steps, my original logic for the data refresh ran perfectly and the OLE/ODBC error disappeared.
I realize this is an odd set of steps but it worked for me and had the least impact on my PBIX.
InsureBI I have seen similar issue when updates to power query happens in excel. I would say report this to microsoft.
I could detect the error, it turns out that there was a relationship between two files that one of my collaborators made without knowing that it would affect, I eliminated the realtion between those two documents and it worked, the error disappeared.
I hope they serve
I haven't been able to solve this problem, so I have decided to add some more details to see if that may be the cause of the error.
The table that I am trying to refresh (in PowerBI Desktop) has 5 new columns I have created, such as COUNTYSTATE = [COUNTY] & [STATE]. Are thes additional colums that I created the reason why the refresh is erroring out? Do I need to delete them and re-create them after every refresh?
Note that if I import the new data file into another report in PowerBI desktop and look at the query, every thing in the script matches the query before refresh.
