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
Dom87326
Helper II
Helper II

Bulk replace text values in single step

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.

 

Dom87326_0-1652189973358.png

 

Any help appreciated. 

 

Thanks!

2 ACCEPTED SOLUTIONS
Vijay_A_Verma
Super User
Super User

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"

View solution in original post

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

View solution in original post

3 REPLIES 3
Dom87326
Helper II
Helper II

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

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
Super User
Super User

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"

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