Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
brigittagemes
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
mahoneypat
Employee
Employee

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
mahoneypat
Employee
Employee

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


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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors