1/1/0012 displaying as null in power bi

[bharukc]

I am importing dates from database. Some of the dates and less than 100 year. 1/1/0012, 2/3/0014. All these dates appear as null when it comes to power bi. I am doing data clean up and for that I need to have this exact value displayed and null value to be null value. 

can somebody help me? 

[rohit_singh]

Hi @bharukc ,

Try explicitly converting these values into string format in your SQL query and then load these into PowerBI.
E.g) cast("1/1/0012" as varchar) as dateval

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂

[bharukc]

Hi @rohit_singh ,

i did that and when I change it to date type in power query, it changes back to null in power bi. If i change data type to date in power bi, it defaults to '1/1/2012' instead of 1/1/0012

[rohit_singh]

Hi @bharukc ,

That's really strange. I loaded "01/01/0012" as a text value and changed to date in Power BI and the value didn't change.

Not sure why this might be happening. Could be worthwhile checking your Power BI settings though if that makes any difference

Kind regards,

Rohit

[bharukc]

Hi @rohit_singh 

How does it get displayed in dashboard or report page? I checked each setting and none seemed to make any difference. 

My trouble is having it not displayed in report pages. Power query editor displays it correctly 

[rohit_singh · Accepted solution]

Hi @bharukc ,

I see what you mean now and am able to replicate your issue. It is displayed correctly in Power Query but it is displayed as blank in the report view. If I use the format funcation to change the datatype it changes it to 01/01/2012. If I use the DATE( ) funcation, it gives me 01/01/1912. 

I think this might explain a little bit about what is happening
https://docs.microsoft.com/en-us/dax/date-function-dax

Apologies but I'm not sure if there is any other way to convert the date correctly in the report view. You could try leaving it in text format just to display it correctly, but I'm not sure if that is an option for you.

Kind regards,

Rohit