Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
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! 🙂
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
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
45 | |
31 | |
30 | |
18 | |
17 |