Forum Discussion
Date column replace to NULL or NA
Hi All,
I have a date column which has values 12-31-9999 in a table which I imported to Power BI desktop. I am trying to display NULL or NA for 12-31-9999. I have tried DAX calculation to create new column but it did not work. Can you please let me know how can we change it.
Thanks
Hi Anonymous ,
I can just guess what problem you might be facing. I suggest to add some error description in your questions/responses. Sample data and desired outcome clarity is expected to get the answers asap.
Below code works for me:
Replaces = IF(FORMAT(DatesReplace[Dates],"dd-mm-yyyy")="31-12-9999", "NA","GoodDates")Output was like this:
Since we can not compare date with text, so I had to use FORMAT function. You may also need to tweak as may be needed.
Hope it helps.
Hi Anonymous ,
The NULL or NA is text type. The data is date type. If we still want to change the column type.The error "Expressions that yield variant data-type cannot be used to define calculated columns" would appear.
We can try mahenkj2 's way.
Create a measure.
mEASURE = IF(MAX('Table'[date])=DATE(9999,12,31),"NA","GOOD DATES")Or a column.
Column = IF('Table'[date]=DATE(9999,12,31),"NA","GOOD DATES")If I have misunderstood your meaning, please provide your pbix file without privacy inforamtion and desired output.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
- Ashish_Mathur
Super User
Hi,
Share the link from where i can download your PBI file.
Thanks for responding 😊. Can you please use excel with column value of '12/31/9999'? Since I cannot share the PBI file.
Thanks
- Ashish_Mathur
Hi,
Even if we replace 12/31/9999 in the Date column, there will be mixed data types in that column and that is not adviseable.
mahenkj2Solution Sage
Hi Anonymous ,
I think, it is not good to have date and text in date column is not a good idea and probably it would not be even possible in DAX. Instead, make another column in the same table comparing with "12-31-9999" and if Yes then NA else say "GoodDates" and use this column as fliter/slicer in your report together with datecolumn you use. The similar thing you can do in Power query as well.
Hope it helps.
Hi mahenkj2
I tried creating new column and was comparing using IF condition in DAX. But I was not able to replace the value. Can you please let me know how can I compare and replace?
Thanks
- mahenkj2
Hi Anonymous ,
I can just guess what problem you might be facing. I suggest to add some error description in your questions/responses. Sample data and desired outcome clarity is expected to get the answers asap.
Below code works for me:
Replaces = IF(FORMAT(DatesReplace[Dates],"dd-mm-yyyy")="31-12-9999", "NA","GoodDates")Output was like this:
Since we can not compare date with text, so I had to use FORMAT function. You may also need to tweak as may be needed.
Hope it helps.
Hi Anonymous ,
The NULL or NA is text type. The data is date type. If we still want to change the column type.The error "Expressions that yield variant data-type cannot be used to define calculated columns" would appear.
We can try mahenkj2 's way.
Create a measure.
mEASURE = IF(MAX('Table'[date])=DATE(9999,12,31),"NA","GOOD DATES")Or a column.
Column = IF('Table'[date]=DATE(9999,12,31),"NA","GOOD DATES")If I have misunderstood your meaning, please provide your pbix file without privacy inforamtion and desired output.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
