topic Power Query custom function to replace multiple values in a column in Power Query

Power Query custom function to replace multiple values in a column

brigittagemes — Sat, 20 Jun 2020 14:34:34 GMT

Hi,

I'm tryiing to create a custom function to find and replace values, all in one step. I really would love to solve this problem without an extra table or DAX SWITCH.

Sample file here

Only the last step seems to be executed.

(myFruit as text) =>

let

#"Replace a" = Replacer.ReplaceValue(myFruit, "a","apple"),
#"Replace b" = Replacer.ReplaceValue(myFruit, "b","banana"),
#"Replace l" = Replacer.ReplaceValue(myFruit, "l","lemon")

in

#"Replace l"

What am I missing?

Thanks,

Brigi

Re: Power Query custom function to replace multiple values in a column

mahoneypat — Sat, 20 Jun 2020 20:37:09 GMT

You need to refer to the previous step in each step. Your last step starts with myFruit, so only it is executed. See modified function below:

(myFruit as text) => let #"Replace a" = Replacer.ReplaceValue(myFruit, "a","apple"), #"Replace b" = Replacer.ReplaceValue(#"Replace a", "b","banana"), #"Replace l" = Replacer.ReplaceValue(#"Replace b", "l","lemon") in #"Replace l"

If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.

Regards,

Pat

Re: Power Query custom function to replace multiple values in a column

brigittagemes — Sun, 21 Jun 2020 10:32:15 GMT

Thank you, works perfectly! This is exactly what I was looking for!

I tried referencing before, but I failed. I couldn't find any hints that the first parameter for the Replacer.ReplaceValue can be the previous step itself, instead of "myFruit", the future column reference. I struggled with adding both, which made one extra parameter for the Replacer.ReplaceValue.

Re: Power Query custom function to replace multiple values in a column

mef47 — Tue, 06 Oct 2020 00:51:44 GMT

Hi there

I'm looking to do something similar but cant figure out this code.

Where do I enter the code? as a new column..?

Data is part of a 80x4000 table not just a single row.

I've looked at other solutions that converted data to a list and then recombines but this removes the rest of the data outside the one column being edited.

Data is environmental data.

1. Several numerical columns contain values less than the detection limit and are entered into the raw file as "<0.1", "<0.05", "X" etc. These need to be changed to a value half the detection limit eg. 0.05, 0.025.

2. One column containing classification identifiers needs to be edited for consistency/grouping. eg. "HC1, "HC2", "HC3" all changed to "HC".

Thanks in advance for help!

🙂