Date formatting - 01-01-01, 1899-12-31

[tex628]

Hi,

I'm experiencing some slightly strange behaviour in regard to how dates are displayed in my data.
I'm wondering if there might be anyone that has an idea why the dates are appearing as they are?

To explain the situation I'm using Odata queries to extract data from a Business Central installation and within BC the Finished dates we are refering to are BLANK. 

When the data arrives in my dataflows this is the appearance:


Next up i'm creating a dataset, using the dataflows as a datasource. Within powerquery the dates appear like this:


When the queries are loaded into PBI Desktop the finished date is displayed like this (Both Data and Report view):


Finally when the report is published the date is shown as this in PBI Service (Ignore the 2021... dates):

There are no datatype transformations done at any time during this process. The columns are extracted as datetype directly from the odata query. 

Any ideas?

Br,
Johannes

[PhilipTreacy]

Hi @tex628 

I can't reproduce the issue.  If I have a date 0001-01-01 in PQ then load to PBID, it is changed to blank.  Loading this to PBI Svc it remains blank.

Isn't 30 Dec 1899 is the earliest date PBI supports?  Not sure why you're dates are being converted tothat.

Just try explicitly replacing 0001-01-01 with null in PQ.

Regards

Phil

[tex628]

Hi Phil, 

From my experience all blank dates from BC installations appear to be extracted as 0001-01-01. I don't know why this is the case but before this specific case they have all been properly conveted into blanks once they loaded into desktop and still maintained that value in Service. 

Converting all the 001-01-01 values into null values would of cource solve the issue but i'd prefer to avoid it if i can simply due to the amount of work it would result in. (I'm working with 40+ tables in each BC installation and 5+ installations) 

If it's unavoidable there's not much to do but I'm rather interested in why the date fields are extracted as 0001-01-01 and why/how they suddenly can be considered 1899-12-31 once they are brought into Service. 

Not knowing if a blank date will be considered BLANK() or "1899-12-31" for calculations is quite problematic. 

Br,
Johannes