Merge multiple ReplaceValue steps

[LittleTimmy]

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.

[v-xuding-msft]

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"

[Jimmy801]

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]

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

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