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
StuartSmith
Power Participant
Power Participant

OLE DB or ODBC error: Exception from HRESULT: 0x80040E1D - Due to Whole Number to Date type change.

All, am working with an Excel file that has a column containing dates with custom formatting "mmmm yy".  When its imported into Power Bi, the date column is imported as "Whole Number" type and therefore I change the data type to "Date" and this updated the column to show the date and not a whole number.  But once thats been done, upon a refresh, the below error happens. I have checked the version of Bi I am running and its the current one, plus tried enabling\disabling "Auto Date\Time" in options.

 

Any Ideas?

 

2020-09-09_09-15-59.jpg

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@StuartSmith , Check data loaded there should some row having a problem.

Check does show row error in any row.

My suggestion would me create a date using this column instead of changing the type .

 

refer how to handle error in power query
https://www.youtube.com/watch?v=OE6DPmKqN7s
https://www.youtube.com/watch?v=9-Lag0VOiTs

 

View solution in original post

5 REPLIES 5
amitchandak
Super User
Super User

@StuartSmith , Check data loaded there should some row having a problem.

Check does show row error in any row.

My suggestion would me create a date using this column instead of changing the type .

 

refer how to handle error in power query
https://www.youtube.com/watch?v=OE6DPmKqN7s
https://www.youtube.com/watch?v=9-Lag0VOiTs

 

When yu say check data load, do you mean here?

 

2020-09-09_09-57-07.jpg

and your comment about creating a a new "Date" column, do you mean create a new column by converting the whole number into a date?  if so, do you know what the correct DAX would be? to create the new column?

 

Thanks in advance,

@StuartSmith , refer this month year to date can help you

https://youtu.be/cJqgphIHXz8

 

Also, can you share the same data, which is creating the problem in text format

ok, figured it out. I created a new column using...

 

Invoice Date = SpendbyVendorRegionVendorCoun[Explicit Invoice Ymv]
 
and then converted to date type.  Refreshed the data and more errors.
 
I suspect there might be a more refined way to convert to date within the code.
 
 
StuartSmith
Power Participant
Power Participant

UPDATE: I have found that if i import the excel file with the original date column name it causes the error, but if i change the date column name to "Date" and then import, it works fine.

 

Imported excel file with date column name unchanged.

2020-09-09_09-41-47.jpg  

 

Imported excel file with date column name changed to "Date" and it works.

2020-09-09_09-43-00.jpg

 

Obviously, i no longer get the error, but means I would have to update the column name of the excel file that contains the date everytime a fresh data set is available.  Any ideas why this is happening and how to fix without having to rename the excel column.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.