Replacing a value in a column with other value in DAX

[efeurich]

So I have looked around on the interweb but can't find what I need.

I need to replace a line of text like "Supermarket jada jada accounting stuff happy day" with the value "Food and Drinks".

The focus here is on supermarket, when this word is in the text it should replace the whole text with the value "Food and Drinks" 

I got it to the point that it replaces the word Supermarket with Food and Drinks. But I need the whole text to be replaced. 

Found some DAX code that uses "using CONTAINSSTRING" but I can't get it to work.

Suggestions are very welcome.

[efeurich]

Hi Micheal,

Thanks for the clear and effective explanation.

Best regards,

Eric

[Mikelytics]

Hi Eric,

I am happy to help! Thank you for your feedback!

Best regards

Michael

-----------------------------------------------------

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!

@ me in replies or I'll lose your thread.

[Mikelytics · Accepted solution]

Hi @efeurich 

Please try the following:

my sample table created in power query and loaded into the data model:

Calculated Column:

Formula to replace the string:

ReplaceText = 
IF(
    CONTAINSSTRING([Text],"supermarket") = TRUE(),
    "supermarket",
    [Text]
)

Result:

Best regards

Michael

-----------------------------------------------------

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!

@ me in replies or I'll lose your thread.