Forum Discussion

brigittagemes's avatar
brigittagemes
Helper I
6 years ago
Solved

Power Query custom function to replace multiple values in a column

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   O...
Need Help
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    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

mahoneypat's avatar
mahoneypat
Microsoft Employee
6 years ago

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

  • brigittagemes's avatar
    brigittagemes
    Helper I
    6 years ago

    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.

  • mef47's avatar
    mef47
    Regular Visitor
    5 years ago

    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!

    🙂