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
LittleTimmy
New Member

Merge multiple ReplaceValue steps

Hi,

 

First time posting.

 

I have multiple Applied Steps, and multiple ReplaceValue statements as follows:

 

Step 1: "= Table.ReplaceValue(#"Changed Type","Fresh Apple","Apple",Replacer.ReplaceText,{"Fruit"})"

Step 2: "= Table.ReplaceValue(#"Replaced Value1","Fresh Orange","Orange",Replacer.ReplaceText,{"Fruit"})"

Step 3: "= Table.ReplaceValue(#"Replaced Value2","Fresh Pear","Pear",Replacer.ReplaceText,{"Fruit"})"

 

To keep the steps cleaner, is there a way to combine these Replace Values into one?

 

Thanks.

3 REPLIES 3
v-xuding-msft
Community Support
Community Support

Hi @LittleTimmy ,

 

There are 3 ways that you can have a try.

  • All replace
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcitKLc5QcCwoyElVitWB8f2LEvPSkQUCUhOLwFz/kozUomKl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Fruit = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Fruit", type text}}),
    Custom1 = [#"Fresh Apple"= "Apple" ,  #"Fresh Orange" ="Orange", #"Fresh Pear" = "Pear"],
    #"Converted to Table" = Table.TransformColumns(#"Changed Type",{{"Fruit",each Record.FieldOrDefault(Custom1,_,_)}})
in
    #"Converted to Table"
  • Split column
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcitKLc5QcCwoyElVitWB8f2LEvPSkQUCUhOLlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Fruit = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Fruit", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Fruit", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Fruit.1", "Fruit.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Fruit.1", type text}, {"Fruit.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Fruit.1"})
in
    #"Removed Columns"
  • create a conditional column
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcitKLc5QcCwoyElVitWB8f2LEvPSkQUCUhOLlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Fruit = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Fruit", type text}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [Fruit] = "Fresh Apple" then "Apple" else if [Fruit] = "Fresh Orange" then "Orange" else if [Fruit] = "Fresh Pear" then "Pear" else [Fruit]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Fruit"})
in
    #"Removed Columns"

 

 

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Jimmy801
Community Champion
Community Champion

Hello @LittleTimmy 

 

check out this code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcitKLc5QyC9KzEtPVYrVgQkkFhTkYOHHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Fruit = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Fruit", type text}}),
    ReplaceMore = Table.ReplaceValue
    (
        #"Changed Type",
        each [Fruit],
        each if [Fruit]= "Fresh orange" then "orange" else if [Fruit]="Fresh apple" then "apple" else [Fruit],
        Replacer.ReplaceText,
        {"Fruit"}
    )
in
    ReplaceMore

 

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

Anonymous
Not applicable

The short answer to your question is: "yes!".

 

The verbode answer is take a look at the threads that appear next to your post:

 

image.png

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