Bulk replace text values in single step

[Dom87326]

Hello, 

I'm trying to replace multiple text values in one power query step. In the example below, New York and Los Angeles should be changed to Boston.

Any help appreciated. 

Thanks!

[Vijay_A_Verma · Accepted solution]

In a Custom column put following

= if List.Contains({"New York","Los Angeles"},[Column A]) then "Boston" else [Column A]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kstV4jML8pWitWJVnLKLy7JzwMzffKLFRzz0lNzUovBfI/8Uricb2ZibqZSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column A" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Column B", each if List.Contains({"New York","Los Angeles"},[Column A]) then "Boston" else [Column A])
in
    #"Added Custom"

[Dom87326]

@Vijay_A_Verma could you please advise how to use it in the same column? 

[Vijay_A_Verma · Accepted solution]

For same column, insert this step where Source should be replaced with your previous step

= Table.ReplaceValue(Source,each [Column A],each if List.Contains({"New York","Los Angeles"},[Column A]) then "Boston" else [Column A],Replacer.ReplaceValue,{"Column A"})

[Vijay_A_Verma · Accepted solution]

In a Custom column put following

= if List.Contains({"New York","Los Angeles"},[Column A]) then "Boston" else [Column A]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kstV4jML8pWitWJVnLKLy7JzwMzffKLFRzz0lNzUovBfI/8Uricb2ZibqZSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column A" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Column B", each if List.Contains({"New York","Los Angeles"},[Column A]) then "Boston" else [Column A])
in
    #"Added Custom"