Date column replace to NULL or NA

[Anonymous]

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

[mahenkj2 · Accepted solution]

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.

[v-rongtiep-msft · Accepted solution]

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.

[mahenkj2]

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.

[Anonymous]

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 · Accepted solution]

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.

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[Anonymous]

Hi @Ashish_Mathur 

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.