Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
susheel1347
Helper III
Helper III

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

2 ACCEPTED SOLUTIONS

Hi @susheel1347 ,

 

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:

 

mahenkj2_0-1654446560070.png

 

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.

 

 

 

View solution in original post

v-rongtiep-msft
Community Support
Community Support

Hi @susheel1347 ,

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")

vpollymsft_0-1654580823048.png

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.

View solution in original post

7 REPLIES 7
v-rongtiep-msft
Community Support
Community Support

Hi @susheel1347 ,

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")

vpollymsft_0-1654580823048.png

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
Impactful Individual
Impactful Individual

Hi @susheel1347 ,

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

Hi @susheel1347 ,

 

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:

 

mahenkj2_0-1654446560070.png

 

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
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

 

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.