Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
InsureBI
Advocate II
Advocate II

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 48
ramdasanudas
Regular Visitor

 
Debster
Regular Visitor

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. 

Anonymous
Not applicable

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 😊

Anonymous
Not applicable

Anonymous
Not applicable

Hello,

I came acroos this same error yesterday in one of my PBI reports.  I tried a lot of the suggetions I came across pn this forum, but none seem to work.  I just pyulling in a notes Excel spreadsheet into my PBI.  Wierd thing is my co worker can open the file and refresh just fine.  Anyone have this issue or know anythign about it?

 

Im getting this error: OLE DB or ODBC erro: the handle is invalid.. A connection could not be made to the data source with the name of '675abf08-3e93-4158-ae32-23f0fd4b3f4d

Hello, I got the same code reference some seconds after I launched the "Refresh All".

 

After reading all the previous posts and doing a lot of attempt I discovered I solved by isolating and unflagging one specific "preview feature" among the Options:

 

"Store datasets using enhanced metadata format."

 

Now, without that option, my repor works perfectly as before the latest PBI Desktop update.

 

I read on the guide of the feature that PBI is investing on this feature for future release, and the fact it is not working on my project makes me fear I will have many problems in feature. I hope PBI engineers will fix this collateral effects (I thing it is RAM related, because I saw in the task manager that the process is really RAM-demanding if the option is active, till the crash happens).

 

Hope this can be useful for people not having solved still.

 

Thank you everybody for all suggestions.

Sonam_Pillai
Helper I
Helper I

I have a solution on this.

 

Go to power Query editor section in Power BI.

 

Create a duplicate query and delete the old one.

 

this will solve your problem.

vsteinly
Frequent Visitor

I'm having the same issue and I'm using an Excel file for my data.  I just upload the lastest update.  Could this have something to do with it?

godfreyan
Frequent Visitor

I am having the same problem with an Excel sheet.Power BI error message.jpg

Anonymous
Not applicable

I have the same error when using local Excel data and append queries from multiple tables.

RV1
Helper I
Helper I

Hi there,

 

I have started getting the same error in the last few days. My model is a combination of SQL Server Tables. Excel files and Aggregated Tables. I have tried the following:

 

a. Checked the integrity and data format of my Excel Files

b. Checked the relationships between tables

c. Rebooted the system

 

I have asked the IT team to reboot the SQL server tonight.

 

Despite all efforts, I keep getting the errors. And each time, the error is on a different table. However, if I refresh the tables individually, they work just fine.

 

Is anybody able to point me to the right direction?

 

Many thanks.

OK. I am sorted. We had a duplicate record. As soon as that was fixed, all errors disappeared. But I had to refresh each table individually to narrow down the problem.

cuiping
Frequent Visitor

i hv the same issue too, seems like as long my table contains "date", this error msg are quite likely to reoccur whenever i refresh the data with "date' column, not sure is it becoz the "date"formate different from Power BI default setting, "mm/dd/yyyy" or "dd/mm/yyyy"

Anonymous
Not applicable

I have made a work around for this issue.  It seems there is an issue going from the SharePoint cloud with an .xlsx file to Power Bi.

 

  • We made a folder, with the excel file in it, in a network drive and set up a data gateway in power bi directly to that file. 
  • In Power Bi Desktop you use the same file to generate your report and publish it while making sure to all page and report level filters you would like to that report.
  • After publishing go into the settings of that data set and add that gateway to it. 
  • This will allow you to update the background data and the report will update with that data.

I hope this helps.

JaZziMine
New Member

Hi, I had the same problem as yours today, and I solved it by deleting some incorrect relationships between different data sources. 

 

Hope it helps!

dipakh
Frequent Visitor

I restarted my pc to clear any errors in ODBC connection and then refreshed the datafiles and the error above disappeared.

I had this issue when importing in multiple spreadsheets and refreshing.

 

I resolved this in two steps:

 

  1. Identify the excel spreadsheet causing the issue.
  2. Step through the Advanced query editor and identify the ofending line

 

In order to complete step 1. I opened up Query Editor and refeshed each source spreadsheet at a time, until I hit the error. This identifies whe soure of the issue.

 

In order to complete step 2. I then opened up Advanced Query Editor and copied the text into Notepad. I then removed all of the steps and added them in one by one and refreshed each time, until I hit the error again. Things to remember when doing this (I found out by trial and error)

 

In my case, adding them all back in, line by line still resolved the issue, so this does look like a bug, but is easily resolved.

rafix1983
New Member

Hi, maybe it's not a proper woraround, but ... In my case helped to do all calculation on the tabs coppied from those upload to M BI. So as a rule I upload all the data I need for the project, than merged, coppied etc. to new tabels and later functionalities are build on those new. Original data are keep in the project without any manipulation, one I do and it's works is lookupvalue from the original tabels. I can assume that if we taking the data from original tabels it's fine for them, when we are try to add some data it's causing the problems with later updates.

 

Hope it's help you and do not affect efficiency of yours projects

rafix1983
New Member

Hi, maybe it's not a proper woraround, but ... In my case helped to do all calculation on the tabs coppied from those upload to M BI. So as a rule I upload all the data I need for the project, than merged, coppied etc. to new tabels and later functionalities are build on those new. Original data are keep in the project without any manipulation, one I do and it's works is lookupvalue from the original tabels. I can assume that if we taking the data from original tabels it's fine for them, when we are try to add some data it's causing the problems with later updates.

 

Hope it's help you and do not affect efficiency of yours projects

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.