Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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,
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,
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"))