Data Refresh Error and All Zero Dates

[Anonymous]

Hello. I am having an issue when I attemp to do a data refresh from the PBI browser based tool. It refreshes fine from the desktop I am getting the following error:

Data source error:

Not a legal OleAut date.. The exception was raised by the IDataReader interface. Table: hbdg customer_order_line_item.

I found and fixed some obvious date problems where the year obviously wrong (0217 instead of 2017, etc.), but am still getting the error on this table. I have many entries where the date shows NULL and many where the date shows 0000-00-00 and my question is does anyone know if the zeros can cause the refresh to fail? Thanks!

J

[SteveCampbell · Accepted solution]

Yes, this would cause an error (see this thread).

The easiest way to fix it without doing it manually:

- Open the query editor

- Click the Add Column tab and click Cusom Column.

 - Enter:

   if [Date] > #date(1900, 1, 1) then [Date] else null

      (if your Date column is called something else, replace [Date] with your column name

- Copy the name of the old Date Column

- Delete the old Date column 

- Rename the new column as exactly what the old column was (that you copied)

Love hearing about Power BI tips, jobs and news?
I love to share about these - connect with me!

Stay up to date on  
Read my blogs on  

Remember to spread knowledge in the community when you can! 

[Anonymous]

Woohoo! Thank you Steve!!!