Forum Discussion
blank dates causing issues in Power BI service
I have a datetime field with contains several blank records.
In Power BI desktop these are shown as blank, and in Power BI service the same. However once the first refresh in Power BI service runs, the blank dates are replaced with '30/12/1899'.
Apart from how annoying that is, it also causes a major issue in a date comparison field where I do something like this in dax:
if(isblank(DateX), true, false)
I've tried several solutions already:
- Changed the MySQL data source to provide null values instead of 0 when date is empty
- Changed 0 values to null values with power query by 'replace value'.
- Changed the column from datetime to date
- Changed the type in powerquery with 'use locale' with several options (But who knows which one to choose)
All lead to the same outcome; fine in power bi desktop, fine when published, messed up when power bi service refreshes.
Now I know I can probably work around this by adding to the if statement something like DateX = '30/12/1899'.
But I can't imagine that to be the most elegant solution.
Is there any way to find out what I have to change to my column/date settings so power bi service treats 0/null values the same as power bi desktop?
Thanks for your help.
just to be clear, 0 and null are not the same thing. Depending on your timezone the datetime value 0 corresponds to 1899-12-31 or 1899-12-30. That's Microsoft's definition of when the epoch starts.
is it possible that your MySQL source is a bit cavalier in sometimes giving you 0 and sometimes null ?
3 Replies
just to be clear, 0 and null are not the same thing. Depending on your timezone the datetime value 0 corresponds to 1899-12-31 or 1899-12-30. That's Microsoft's definition of when the epoch starts.
is it possible that your MySQL source is a bit cavalier in sometimes giving you 0 and sometimes null ?
of course. Therefore I've changed the MySQL view to replace 0 values with null values just to be sure, same result. Still; why do I not see these 1899 dates in power bi desktop: perfectly blank in desktop.
I ran into something similar, and I would suggest replacing all with nulls in using M Query.
Add steps to replace the BLANKS and " 0 " values....or any value.....
or another more dynmaic way to go about mabe is:
if the %Value_Type% = "Date" then Value,
else if % Some Other Expression % then ........
else NULL
