topic Re: Bulk replace text values in single step in Power Query

Bulk replace text values in single step

Dom87326 — Tue, 10 May 2022 13:41:43 GMT

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!

Re: Bulk replace text values in single step

Vijay_A_Verma — Tue, 10 May 2022 14:08:42 GMT

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"

Re: Bulk replace text values in single step

Dom87326 — Tue, 10 May 2022 14:18:25 GMT

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

Re: Bulk replace text values in single step

Vijay_A_Verma — Tue, 10 May 2022 14:24:22 GMT

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"})