cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

replacing specific text within Cells

I have several instances where "9-23-43-29E6" is mentioned in various cells in various forms (see below for some examples). I would like to replace only the "9-23-43-29E6" text. but it looks like the replace function looks for the whole cell to match. How can i do this in a broad reaching way as i have a lot of references to it in different forms.

 

Cell examples where the text will need to be repalced

 

9-23-43-29E6 - Stage 0

A9-23-43-29E6 - Stage 1

B9-23-43-29E6 - Stage 2

B9-23-43-29E6 - Stage 2, seen from 9-23-43-29E6

3 REPLIES 3
v-lid-msft
Community Support
Community Support

Hi @Anonymous ,

 

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?


Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-lid-msft
Community Support
Community Support

Hi @Anonymous ,

 

We can try to use a replace step to meet your requirement:

 

= Table.ReplaceValue(#"Changed Type",each [Column],each if Text.StartsWith([Column],"9-23-43-29E6") or Text.EndsWith([Column],"9-23-43-29E6")  then Text.Replace([Column],"9-23-43-29E6","")  else Text.Replace([Column]," 9-23-43-29E6 ","")
,Replacer.ReplaceValue,{"Column"})

 

5.jpg

 

All the queries are here:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WstQ1MtY1MdY1snQ1U9BVCC5JTE9VMFCK1YlWcsQqZwiWc8IqZ4RPTkehODU1TyGtKD9XAVmFUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",each [Column],each if Text.StartsWith([Column],"9-23-43-29E6") or Text.EndsWith([Column],"9-23-43-29E6") 
                                                        then Text.Replace([Column],"9-23-43-29E6","") 
                                                        else Text.Replace([Column]," 9-23-43-29E6 ","")
,Replacer.ReplaceValue,{"Column"})
in
    #"Replaced Value"

 

If it doesn't meet your requirement, Could you please show the exact expected result based on the tables that you have shared?


By the way, PBIX file as attached.


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
HotChilli
Super User II
Super User II

You can use the Text.Replace   function in Power Query, for example,

Table.AddColumn(#"Changed Type", "Custom", each Text.Replace([Column1], "9-23-43-29E6", "XXXX"))

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors