topic Replace multiple values in one step using DAX in DAX Commands and Tips

Replace multiple values in one step using DAX

lnayak — Wed, 23 Feb 2022 14:00:13 GMT

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!

Re: Replace multiple values in one step using DAX

Anonymous — Wed, 23 Feb 2022 15:26:03 GMT

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/

Re: Replace multiple values in one step using DAX

lnayak — Thu, 24 Feb 2022 07:41:45 GMT

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,

Re: Replace multiple values in one step using DAX

Anonymous — Thu, 24 Feb 2022 08:06:17 GMT

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

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

Re: Replace multiple values in one step using DAX

BeaBF — Thu, 24 Feb 2022 09:46:29 GMT

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

Re: Replace multiple values in one step using DAX

lnayak — Fri, 25 Feb 2022 14:07:22 GMT

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!

Re: Replace multiple values in one step using DAX

BeaBF — Fri, 25 Feb 2022 15:15:37 GMT

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 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.