Forum Discussion

StephenF's avatar
StephenF
Responsive Resident
7 years ago
Solved

Single Wildcard with replace for text cleansing

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...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    StephenF 

     

    You can use Extract Text Before Delimiter and specify "S1" there

     

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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