replacing specific text within Cells

[Anonymous]

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

[v-lid-msft]

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,

[v-lid-msft]

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

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,

[HotChilli]

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