cancel
Showing results for 
Search instead for 
Did you mean: 
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.

45 REPLIES 45
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

 

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

sbliley
New Member

Try 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.

Anonymous
Not applicable

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.

Helpful resources

Announcements
Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.