Not a legal OleAut Date

[keno]

I'm getting this error when a schduled refresh runs (it doesn't error when manually refreshing). I've found the reason why this happens in this forum, e.g. ANSWERED QUESTION.

Data source error:	Not a legal OleAut date.. The exception was raised by the IDataReader interface. Table: Manual Attendance Full.
Cluster URI:	WABI-EUROPE-NORTH-B-redirect.analysis.windows.net
Activity ID:	452648da-2252-4845-88de-515b0dd58161
Request ID:	8a1ec65c-a4a4-4c79-bb04-62807acc2ecc
Time:	2019-02-12 07:24:10Z

The question is WHY and HOW is this an error. Dates before 1/1/1900 are more than valid, I don't understand why Power BI would kick them out. And why Power BI would kick them out on a schduled refresh, of all things.

I'm purposefully displaying dates that are not 100% correct as this dashboard is to help us validate incorrect scans of documents.

Does anyone have a work around? I don't want to lose the incorrect data as I need the entire dataset to be present.

Thank you,

Ken.

[Abigail_Morgan]

It seems like your issue is related to how Power BI handles date formats during scheduled refreshes, particularly with invalid dates. One suggestion is to preprocess your dataset to replace these problematic dates with placeholders or flag them for exclusion during the refresh. You might also consider setting a default date range within Power BI's query editor to avoid such errors.

If you're dealing with legal or public data like attendance records or law enforcement reports, tools for managing Ky criminal records or conducting a Kentucky background check can offer insights into structured data handling. For related info, you can explore Ky inmate search. It might help streamline data validation processes in a broader context

[Stachu]

dates like this are allowed in M/Query Editor (page 35)
https://docs.microsoft.com/en-gb/powerquery-m/power-query-m-language-specification

but not in DAX
https://docs.microsoft.com/en-us/dax/date-function-dax

there are also some examples there with dates before 1899, but it doesn't look very promising

if you have such a wide year range do you use months/days at all? maybe it will be easier to just treat date as a numeric dimension rather than date

[keno]

The dates need to be readable by humans, so the date format of dd/mm/yyyy is the best one to use. 

This is for correcting errors. I don't really understand how they could build a system that only looks at dates after 1/1/1900.

I guess the real question is, why do they allow it for manual refreshes, but not scheduled refreshed?

[v-yuezhe-msft]

@keno,

A worksaround I can think of is to change the data type of the date field to text or use manual refresh instead of schedule refresh.

Regards,
Lydia