Manual or scheduled refresh failing to Not a legal OleAut date - how to trace further?

[digipics]

Hi all

I've got a large data set with mutliple data sources and hundreds of thousands of rows in a working report/dashboard deployed to the app/service. Refreshing all queries in Desktop works fine. Scheduled or manual refreshes in the Service tier fail with a Not a legal OleAut date error.

I've read from this forum that it most likely is being caused by bad data in a date column somewhere (to which I have probably 40 places to look).  How can I trace this error further and see exactly where it is failing?

Thanks in advance. Here's the error--

7/15/2017, 10:37:07 PM	7/15/2017, 10:37:59 PM	Failed	There was an error when processing the data in the dataset.
Data source error: Not a legal OleAut date.. The exception was raised by the IDataReader interface. Cluster URI: WABI-WEST-US-redirect.analysis.windows.net Activity ID: bb3451ac-4690-4481-895b-b326f1bd7c34 Request ID: e0a20926-e7a8-46f7-9b38-9bd23d608413 Time: 2017-07-16 02:37:59Z

[janmortensen]

Hi, I have also run into this particular problem. A first check of my data revealed faulty-dates as mentioned here and in other entries on this forum. We fixed the dates in the source and a check in PowerBI Desktop shows that the rest of the dates should be valid (unless there is an issue with the year 9999?).

So to re-iterate the problem:

1. Import of data-source in PowerBI Desktop is faultless.
2. Published and updated in PowerBI online.
3. Scheduled update fails with "Not a legal OleAut date.. The exception was raised by the IDataReader interface. Table: ..."

[digipics]

@janmortensen

Thanks for the comments. Where did you get the details for why/what failed on the PowerBI Service (not Desktop) refresh? I'm struggling to even get details as to what/why is failing. Thanks

[janmortensen]

@digipics - I got the general error-message from the schedule update-page. But the it was the before-mentioned generic message that started this topic in the first place. I actually found that I had more faulty dates in my dataset (used excel to look at the data) and also found that the source could not easily fix these errors. I could probably have fixed the issue with some powerquery-logic on import, but I chose in the end to not import the columns that caused trouble and focus my visualization on other parts of the data.

[v-ljerr-msft]

Hi @digipics,

---

I've read from this forum that it most likely is being caused by bad data in a date column somewhere (to which I have probably 40 places to look).  How can I trace this error further and see exactly where it is failing?

---

Yes, the above error usually occurs when there are incorrect date values such as 11/09/0016 in the data source. 

To solve this issue, you can sort the date column of each data source first in query editor, then check minimum date value and maximum value to identify incorrect date value. After that, correct those illegal date values, then re-publish the Power BI Desktop file to Power BI Service and check if you can refresh the dataset successfully. 

Regards

[digipics]

Thanks for the reply. It is a daunting task to do that in my data set. Anybody know how I can get more details on the online refresh error to see which column it is failing on?

Is there anybody from MS Support that monitors these threads that could help trace it?

thanks