Forum Discussion

lnayak's avatar
lnayak
Frequent Visitor
4 years ago
Solved

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 an...
  • BeaBF's avatar
    BeaBF
    4 years ago

    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's avatar
    lnayak
    Frequent Visitor
    4 years ago

    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,