Forum Discussion
Replace multiple values in one step using DAX
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