Not a legal OleAut date. Power BI

BY: Collin Quiring

We started receiving an error message in Power BI during the refresh via the Power BI Data Gateway. The error only appears online – it does not occur when refreshing, saving or publishing the file in the Power BI Desktop.

The message is relatively simple but can be complex to fix. The error message is:
Data source error: Not a legal OleAut date.. The exception was raised by the IDataReader interface. Table:

It then lists the Table name, the Cluster URI, Activity ID, Request ID and Time of the error.

After some research, it appears there are a few potential causes for this issue.
The first cause is most often bad data. In this case, the message points to a “date” type of data. In looking at the data that I was using I definitely had bad dates. Here are some examples:

If you have trouble seeing that image, some of the bad, unrealistic, dates in my data are:
May 3, 0207
March 23, 0208
March 1, 0919

If you are having trouble seeing that image, here are some of the bad dates:

3/29/0219

11/24/0218

6/4/0181

The initial solution seemed “easy” enough.  I went through the Edit Queries and modified each and every date (using the Replace Values function) to be a correct date.  That worked fine in Desktop (but it had already worked in desktop) but no joy in the service – I still got the OleAut error.

I made a special visual with each and every date field in my table that was causing the problem.  I found one that had a blank field in it.  That may or may not have been an issue but I removed the blanks (not necessarily null).

Still, the error appeared on each and every refresh in the service via the gateway.

In my research, some folks appear to have an issue in their links in their relationships.  But, I wasn’t using a date link in this table – only ID fields for the relationships.  In fact, there was no DateTime table at all due to the needs of this report.

There were some date fields in the table that I wasn’t using so I changed those fields to text rather than trying to fix the bad dates in those fields.

Still no joy.

I took all of the date fields, changed the type from Date to Number and the changed it back.

Still no joy.

So, I put in a step between the changing back and forth the types.  In my case, I just added a column with a text field.

And finally – there was joy in the land!

Comments