March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
I wish to do as you would in excel's search replace:
Replace ~(ignore double quotes here)
" S1*" - (S1 followed by a wildcard
to be replaced with "" ie no text
EG
"Mario S1 E1" Would be replaced with "Mario".
asterisk doesnt seem to work in replace values in the query editor however.
Any way to do the same?
Solved! Go to Solution.
You can use Extract Text Before Delimiter and specify "S1" there
Hi @StephenF ,
You can add a column with condition to replace value, please take a look at following query.
#"Added Custom" = Table.AddColumn(#"Changed Type", "Replace", each try if Text.Split([Text]," S1")<>null then Text.Split([Text]," S1"){0} else [Text] otherwise [Text])
Full query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k0sysxXCDZUcDVUitWJVgpJLS4Bco3AHLgkRM7RyRXESS1CkdQyMlWKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Text = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Text", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Replace", each try if Text.Split([Text]," S1")<>null then Text.Split([Text]," S1"){0} else [Text] otherwise [Text]) in #"Added Custom"
Regards,
Xiaoxin Sheng
You can use Extract Text Before Delimiter and specify "S1" there
@Zubair_Muhammad Strictly this works, but if i chain a bunch of these it only picks up the last one.
The procession doesnt save the progress of the transformation on the string after each step for this function which is a bit unusual.
eg:
#"Extracted Text Before Delimiter1" = Table.TransformColumns(#"Replaced Value", {{"Programme Name", each Text.BeforeDelimiter(_, " S1"), type text}}), #"Extracted Text Before Delimiter2" = Table.TransformColumns(#"Replaced Value", {{"Programme Name", each Text.BeforeDelimiter(_, " S3"), type text}}), #"Extracted Text Before Delimiter3" = Table.TransformColumns(#"Replaced Value", {{"Programme Name", each Text.BeforeDelimiter(_, " S4"), type text}}), #"Extracted Text Before Delimiter4" = Table.TransformColumns(#"Replaced Value", {{"Programme Name", each Text.BeforeDelimiter(_, " S5"), type text}}),
EDIT: Oh right I need to change #"Replaced Value" to point to previous row also on each row for it to work !
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
134 | |
91 | |
90 | |
64 | |
58 |
User | Count |
---|---|
201 | |
137 | |
107 | |
72 | |
68 |