Replace multiple values in one step using DAX

[lnayak]

Hi All,

I have a company column with multiple common text ending with "Ltd", "Limited", "Inc", "Ltd.", "Company". I want to create a single DAX function to remove/replace these text with blank and return the value before these text. Eg:- "Facebook India Limited" to return the value "Facebook India". 
Can anyone help me with the appropriate DAX function? I know i can use replace values in Power Query but that is time consuming, am looking for a single DAX function to replace these values in one step.

Thanks in advance!

[BeaBF · Accepted solution]

@lnayak Hi!

Try with this step:

#"Substitute" = Table.TransformColumns(PREVIOUS STEP,{{"COLUMN NAME", each List.Accumulate({{"Ltd",""},{"Limited",""},{"Inc",""},{"Ltd.",""},{"Company",""}},_,(string,replace) => Text.Replace(string,replace{0},replace{1}))}})

BF

[lnayak]

Thanks @BeaBF,

If you could help me get this output by using Text.BeforeDelimit function which checks for any text that ends with "Limited", "LTD", "Ltd", "ltd" ,"(" and also use ignore case to avoid adding similar text with different case.

Am very new to DAX and would love to explore new things!

Thanks!

[BeaBF]

@lnayak I'm not sure I understand, but I try!
Think you have this start table:

Colonna1

Facebook India Limited

Facebook India Ltd

Facebook India Ltd.

ciao pippo

ciao pluto

ciao Ltd

Limited ciao

ciao Inc ltd

ciao inc ltd

This step: #"Substitute" = Table.TransformColumns(#"Modificato tipo", {{"Colonna1", each if
Text.EndsWith(_,"Limited" , Comparer.OrdinalIgnoreCase ) or Text.EndsWith(_,"Ltd", Comparer.OrdinalIgnoreCase) or Text.EndsWith(_,"(", Comparer.OrdinalIgnoreCase) then
List.Accumulate({{"Ltd",""},{"Limited",""},{"Inc",""},{"Ltd.",""},{"Company",""}},_,(string,replace) => Text.Replace(string,replace{0},replace{1}))
else _, type text}}) 

Check if the string ends with Limited or Ltd or "(" with case INSENSITIVE, then if it is true, substitute the character you put in te second piece of code, like the first one. 

The output will be:

Colonna1

Facebook India

Facebook India

Facebook India Ltd.

ciao pippo

ciao pluto

ciao

Limited ciao

ciao ltd

ciao inc ltd

If I did not understand the request correctly, I ask you to explain it better with examples like I did.

BF

[Anonymous]

I don't know - it could be possible in Dax.

The Tutorial Video shows how this is possible - albeit in M (Data Transformation)

[Anonymous]

Here's a good tutorial: https://www.howtoexcel.org/bulk-replace-values/

Edit: Just noticed: The Tutorial is for M replace. With Dax you can https://dax.guide/replace/ or
https://dax.guide/substitute/

[lnayak]

I tried above one but it looks like I need to hard code each value is '<old text> to replace and get the output with a replaced text.

= Table.ReplaceValue(#"Replaced Value","LIMITED","",Replacer.ReplaceText,{"Supplier - Clean"})

= Table.ReplaceValue(#"Added Custom2","Ltd","",Replacer.ReplaceText,{"Supplier - Clean"})

Can i get a solution to club in all items to be replaced {"LIMITED", "Ltd"} into one and to replace with {" ", " "} all at once. 

Many Thanks,