Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Remove Text between a pattern of string

I have a Column with data as below "/Data/Bucket-Folder/Heritage-Bucket-Folder/2019/09/02/01/30/newtext" Here i would like remove the pattern of text starting with numbers/dates as following  2019...
Need Help
  • AnkitBI's avatar
    AnkitBI
    6 years ago

    Anonymous 

    Have you tried this custom column

    Text.Combine(List.Select(Text.Split([Column1],"/"),each Text.Start(_,1) > "A"),"/")

    Thanks
    Ankit Jain

    Do Mark it as solution if the response resolved your problem. Do like the response if it seems good and helpful.

     

AnkitBI's avatar
AnkitBI
Icon for Solution Sage rankSolution Sage
6 years ago

Below is how I am able to achieve it. #"Changed Type"{0}[Column1] is to get the Text Value. You may need to modify as per your data.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WilHSd0ksSdR3Kk3OTi3RdcvPSUkt0vdILcosSUxP1UUVNjIwtNQ3ACIjfQNDfWMD/bzU8pLUipIYJaXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    Custom1 = Text.Combine(List.Select(Text.Split(#"Changed Type"{0}[Column1],"/"),each Text.Start(_,1) > "A" or Text.Start(_,1) = """"),"/")
in
    Custom1

Thanks
Ankit Jain

Do Mark it as solution if the response resolved your problem. Do like the response if it seems good and helpful.