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

[viveksasi94]

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 🙂

[v-cherch-msft]

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

[v-cherch-msft]

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

[ryan_mayu · Accepted solution]

@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])