Forum Discussion
Replace multiple values in one step using DAX
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!
6 Replies
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!
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
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/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,
