cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
viveksasi94 Regular Visitor
Regular Visitor

How to substitute a Date with a String without changing the data type to string

Greetings Datanuts, I have a date column whose data type is Date. I need to replace a specific date in the date column with a String. If I change the data type of the column to Text it is not sorting properly(i.e earliest date should come first). Note - MY_MODEL[Snapshot Date] data type is Date/time TEST = IF(MY_MODEL[Snapshot Date]=31/12/9999,"Live",MY_MODEL[Snapshot Date]) Error - Expressions that yield variant data-type cannot be used to define calculated columns. Any help is appreciated Smiley Happy
1 ACCEPTED SOLUTION

Accepted Solutions
ryan_mayu Established Member
Established Member

Re: How to substitute a Date with a String without changing the data type to string

@viveksasi94

 

I am not sure if I understand your question clearly. I think if you want to return the value as text and date type, you need to unify the type.

 

In my example, the type of data column is date and the type of date2 type is TEXT.

 

Hope this is helpful. Thanks

 

checkdate = if('Table1'[date]=DATE(2018,01,01),"Live",FORMAT('Table1'[date],""))
checktext = if(Table1[date2]="1/1/2018","Live",Table1[date2])

 

c1.JPG

3 REPLIES 3
ryan_mayu Established Member
Established Member

Re: How to substitute a Date with a String without changing the data type to string

@viveksasi94

 

I am not sure if I understand your question clearly. I think if you want to return the value as text and date type, you need to unify the type.

 

In my example, the type of data column is date and the type of date2 type is TEXT.

 

Hope this is helpful. Thanks

 

checkdate = if('Table1'[date]=DATE(2018,01,01),"Live",FORMAT('Table1'[date],""))
checktext = if(Table1[date2]="1/1/2018","Live",Table1[date2])

 

c1.JPG

Community Support Team
Community Support Team

Re: How to substitute a Date with a String without changing the data type to string

Hi @viveksasi94

 

You need to unify the type to "Text" for the column as ryan_mayu said. If you need to sort the column, you may try to add an index column in query editor and use 'Sort by column' by index column.Below are some posts about this feature for your reference.

https://community.powerbi.com/t5/Desktop/Creative-x-axel-in-power-bi-graph/m-p/590607#M280203

https://community.powerbi.com/t5/Desktop/Two-level-sorting-Matrix/m-p/507745#M237204

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team
Community Support Team

Re: How to substitute a Date with a String without changing the data type to string

Hi @viveksasi94

 

Could you tell me if your problem has been solved? If it is,kindly mark the helpful answer as a solution and welcome to share your own solution. More people will benefit from here. If not, please share more details for us so that we could help further on it.

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.