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

[StuartSmith]

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?

[amitchandak · Accepted solution]

@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

[StuartSmith]

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

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,

[amitchandak]

@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

[StuartSmith]

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]

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.

  

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

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.