cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills 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.

47 REPLIES 47
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.

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

Anonymous
Not applicable

I fixed the same issue. In my case it was also a problem of date format.

If you imported once a column of dates but without a 'date' type on Excel then Power BI sees it as a number. In Modify request > step "modified types" there is something like :

= Table.TransformColumnTypes(#"Columns  ....      {{"Source.Name", type text}, {"Date", Int64.Type}, ....

I have solved it by changing {"Date", Int64.Type} to {"Date", type date} .... and by checking my data in Excel is in the correct 'date' type.

naeljb Thank you!! This worked for me as well. It was driving me nuts all day. Thank you!!!!! 

MWalje
Advocate I
Advocate I

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. 

pascalvanhove
New Member

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.

Anonymous
Not applicable

Hey,

 

I tested different documents. Each time one of the cells contained #N/A I got this error. Once I deleted it, the documents exported fine.

Anonymous
Not applicable

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"

Anonymous
Not applicable

Was also experiencing sameissue: 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"

rodesteb
New Member

Hi,

 

I got the same error as trying to refresh the data and solved it by changing the data types of some of my columns from the ADVANCED EDITOR option on the Query Editor window.

 

Regards,

I don't think so this is related to data type when you referesh the table one by one you will not get this error but if you refresh all at one go you might face this also once you refresh all the table one by one and then again try to refersh all table then you will not face this error again.

 

i think this is related to connection even its DB connected locally don't know why this happening,  files are not able to connect but when refereshed individually files its connected,

 

i think this is a bug.

 

Anonymous
Not applicable

hey all,

 

I have the same  issues.

 

I use a number of CSV, Excel files as a source. It will take me at least a day to reproduce my report.

 

Please fix this bug.

rodesteb
New Member

Hi,

 

I got the error as well when trying to refresh the data and solved it by changing the column data types from the ADVANCED EDITOR option on the Query Editor window.

 

Regards,

rovamaq
Frequent Visitor

 

I have the same problem , somebody have the solution to fix this ? 

GrimasS
Regular Visitor

Hi Insure BI,

 

Not sure if you got this resolved or not but take a look at the Relationship Manager and see if there are any conflicting relationships. I get this error when adding new Columns or Tables to my report. I have several relationships between tables in my report and the Relationship Manager will sometimes autodetect new relationships when adding in new Columns or Tables. Some of these newly created relationships will conflict with the old ones. When this happens and  I attempt to refresh the report I get your error. So any time I add new Columns or Tables to my report, I have to double check the Relationship Manager to see if there are any new relationships.

I am having this same problem. Data source is a local SQL table.

 

Any thoughts?

I'm also having this same problem, from excel data sources.

I've reviewed the relationships in my database, and eliminated several of the newest ones, but have not been able to resolve the issue. Any other suggestions?

I gave up on trying to fix the issue and deleted the table giving the error. Then I was able to refresh the remaining data and able to re-add the table.

InsureBI
Advocate II
Advocate II

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.

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

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

Top Solution Authors
Top Kudoed Authors