cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

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

 

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

1 ACCEPTED SOLUTION
Super User IV
Super User IV

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

3 REPLIES 3
Super User IV
Super User IV

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

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!

🙂 

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.

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors