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

42 REPLIES 42
Helper II
Helper II

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

Frequent Visitor

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.

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.

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?

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.

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.

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"

Resolver I
Resolver I

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.

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!

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.

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

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

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.

New Member

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.

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors