Power Query - replace several values within one line

[ruthhacche]

I have this tedious list of code that is about to get longer. It was created using Transform/replace values on the ribbon rather than writing from scratch. I am sure you should not write it like this from scratch but I cannot work out the syntax to do all the replacements in these two columns in just one bit of code.

#"Replaced Value1" = Table.ReplaceValue(#"Clean Names","GAF ","",Replacer.ReplaceText,{"ITEM Name Short","ITEM ProductName"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","BKC ","",Replacer.ReplaceText,{"ITEM Name Short","ITEM ProductName"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","PZA ","",Replacer.ReplaceText,{"ITEM Name Short","ITEM ProductName"}),
#"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","PZC ","",Replacer.ReplaceText,{"ITEM Name Short","ITEM ProductName"}),
#"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4","BKA ","",Replacer.ReplaceText,{"ITEM Name Short","ITEM ProductName"}),
#"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5","GFT ","",Replacer.ReplaceText,{"ITEM Name Short","ITEM ProductName"}),

I guess also there is a way to do this using a separate table of replacement names - can someone point me to where I might find an article on this to look at some time.

 

[sandy666]

Bulk Find And Replace In Power Query | How To Excel

www.howtoexcel.org

 

[shaowu459]

See if this helps. lst is a list of values you want to replace, in my example, the code replace A,B,C to "@".

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    lst = {"A","B","C"},
    res = List.Accumulate(
                          lst,
                          Source,
                          (x,y)=>
                                 Table.ReplaceValue(x,y,"@",Replacer.ReplaceText,{"Data"})
                         )
in
    res

 

[ruthhacche]

I did this - thank you. makes a lot of sense when you see it.

 

[shaowu459]

I did this - thank you. makes a lot of sense when you see it.

Glad we could help and thanks for your feedback

 

[ruthhacche]

Hi again! I used the bulk replace function (named ReplaceTrim) and it is working really well in my table. But something weird is happening. Further down the transformation steps i wanted to do another bulk replace on a different column. I created a second find and replace table and I called the ReplaceTrim function again passing it the parameters of the new find and replace table and the new column I want to be cleaned. But it returned an error as below. If I copied the code from my original ReplaceTrim step (ie the transformation using my original find and replace table) it threw the same error - EVEN though it was exactly the same as the one higher up that worked fine. One of my developers hopped in to help me and we decided to copy the ReplaceTrim function as ReplaceRecode so we could mess with it and the tables to see what might be throwing the error. The exact copy function before any amendments - when passed the parameters of the new find and replace table and the column (ie the exact same as the step that threw the error above but simply using function ReplaceRecode not ReplaceTrim ) - NOW WORKS! We went back and repeated all variations of the above several times - we could only get the second bulk replace step to work by using a second version of the bulk replace function but leaving everything else the same. Do I have to have to 2 versions of a function to be able to call it more than once within one table? It is not a problem, it all works now, but I thought the whole point of a function was you can use it over and over. I can use it on other tables - it is only when I try to use it for a second time in one table that it gets weird.

The two tables are just a find and a replace column. the two functions are identical other than name

using ReplaceTrim the first time worked - ITEM Name Short has been trimmed

Using ReplaceTrim the second time does not work.

Using the exact copy of ReplaceTrim called ReplaceRecode works - (temp has been changed).