power query replace value with If not working

Question

So I am trying to replace the currencies in my report with "blank" . I basically just wanted to get rid of the currency symbols and capture the numbers.

This can be done by doing multiple applied steps of ReplaceValue but hoping I could just simplify it with one applied steps.

let

Source = Excel.CurrentWorkbook(){[Name="Table1\_2"]}[Content], 

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Payment", type text}}), 

#"Replaced Value" = Table.ReplaceValue(#"Changed Type","Rs.","",Replacer.ReplaceText,{"Payment"}), 

#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","RM","",Replacer.ReplaceText,{"Payment"}), 

#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","ï¿¥","",Replacer.ReplaceText,{"Payment"}), 

#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","Php","",Replacer.ReplaceText,{"Payment"}), 

#"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","Rp","",Replacer.ReplaceText,{"Payment"}), 

#"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4","£","",Replacer.ReplaceText,{"Payment"}), 

#"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5","HK$","",Replacer.ReplaceText,{"Payment"}), 

#"Replaced Value7" = Table.ReplaceValue(#"Replaced Value6","¤","",Replacer.ReplaceText,{"Payment"}), 

#"Replaced Value8" = Table.ReplaceValue(#"Replaced Value7","NT$","",Replacer.ReplaceText,{"Payment"}), 

#"Replaced Value9" = Table.ReplaceValue(#"Replaced Value8","￦","",Replacer.ReplaceText,{"Payment"}), 

#"Replaced Value10" = Table.ReplaceValue(#"Replaced Value9","د.Ø¥.â€","",Replacer.ReplaceText,{"Payment"}), 

#"Replaced Value11" = Table.ReplaceValue(#"Replaced Value10","฿","",Replacer.ReplaceText,{"Payment"}) 

in

#"Replaced Value11"

Answer 1

have a look at this... replacing multiple currency symbols with a blank value in your report, you can use a list of currency symbols and apply a single transformation step using the List.Accumulate function. Here's an example of how you can modify your code to achieve this:

Source = Excel.CurrentWorkbook(){[Name="Table1\_2"]}[Content], 

#"Changed Type" = Table.TransformColumnTypes(Source, {{"Payment", type text}}), 

currencySymbols = {"Rs.", "RM", "ï¿¥", "Php", "Rp", "£", "HK$", "¤", "NT$", "￦", "د.Ø¥.â€", "฿"}, 

#"Replaced Value" = Table.TransformColumns(#"Changed Type", {{"Payment", each List.Accumulate(currencySymbols, \_, (state, current) => Text.Replace(state, current, ""))}}) 

in

#"Replaced Value"``` 

In the code above, we define a list called currencySymbols that contains all the currency symbols you want to replace. Then, we use the Table.TransformColumns function to transform the "Payment" column by applying the List.Accumulate function. This function iterates over each currency symbol in the currencySymbols list and replaces it with an empty string in the "Payment" column.

By using this approach, you can simplify the code and replace multiple currency symbols in one transformation step.

Answer 2

Hi,

You may download my solution workbook from here. Add the currency symbols that you want to replace in the second Table. Click on Data > Refresh.

You may also use Column from Examples.

Hope this helps.

Answer 3

Hi Beerderail D!

Thank you for the feedback

I'm sorry unfortunately, I don't have any further troubleshooting to offer.

There are many knowledgeable users active on the forum and I hope that someone else can offer further insight into your issue.

Kind Regards, Shakiru

Answer 4

Hi Shakiru,

Thanks but that is actually what I'm wanting to avoid to have a bunch of replaced values in the applied steps because I have more than then currency symbols that I need to get rid of and I was hoping there should be a better way to do it. Below is what it's currently looks like if we were to apply your solution . So hopefully all the replace value steps can't just be done in one replaced value step.

let

Source = Excel.CurrentWorkbook(){[Name="Table1\_2"]}[Content], 

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Payment", type text}}), 

#"Replaced Value" = Table.ReplaceValue(#"Changed Type","Rs.","",Replacer.ReplaceText,{"Payment"}), 

#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","RM","",Replacer.ReplaceText,{"Payment"}), 

#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","ï¿¥","",Replacer.ReplaceText,{"Payment"}), 

#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","Php","",Replacer.ReplaceText,{"Payment"}), 

#"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","Rp","",Replacer.ReplaceText,{"Payment"}), 

#"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4","£","",Replacer.ReplaceText,{"Payment"}), 

#"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5","HK$","",Replacer.ReplaceText,{"Payment"}), 

#"Replaced Value7" = Table.ReplaceValue(#"Replaced Value6","¤","",Replacer.ReplaceText,{"Payment"}), 

#"Replaced Value8" = Table.ReplaceValue(#"Replaced Value7","NT$","",Replacer.ReplaceText,{"Payment"}), 

#"Replaced Value9" = Table.ReplaceValue(#"Replaced Value8","￦","",Replacer.ReplaceText,{"Payment"}), 

#"Replaced Value10" = Table.ReplaceValue(#"Replaced Value9","د.Ø¥.â€","",Replacer.ReplaceText,{"Payment"}), 

#"Replaced Value11" = Table.ReplaceValue(#"Replaced Value10","฿","",Replacer.ReplaceText,{"Payment"}) 

in

#"Replaced Value11"

Answer 5

Hi Beerderail D!

You can try using the Text.Replace function to replace the specific currency symbols with an empty string.

Please try this: let Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source, {{"Payment", type text}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type", each [Payment], each Text.Replace([Payment], "HK$", ""), Replacer.ReplaceText, {"Payment"}), #"Replaced Value 2" = Table.ReplaceValue(#"Replaced Value", each [Payment], each Text.Replace([Payment], "RM", ""), Replacer.ReplaceText, {"Payment"}) in #"Replaced Value 2"

Make sure to replace the column name "Payment" in the code with the actual column name in your Excel table.

Kindly let me know, if you require additional assistance, I will be glad to help further.

Best Regards, Shakiru