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/09/02/01/30/

The above number/date can change.

 

The final output to be:

"/Data/Bucket-Folder/Heritage-Bucket-Folder/newtext"

How can I do? I tried with the following 

Table.ReplaceValue(#"Removed Top Rows","/2019/09/02/01","",Replacer.ReplaceText,{"Url"})

This replaced only "2019/09/02/01/" Not the following number. These numbers/date change in the string. So cannot use the hardcoded values to replace.

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.

     

9 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    6 years ago

    Anonymous 

    Try this custom column

     

    =Text.Start([ColumnName],Text.PositionOf([ColumnName],"/2"))
&
Text.End([ColumnName],Text.Length([ColumnName])-Text.PositionOf([ColumnName],"/",Occurrence.Last))
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi, Thanks for the solution. I think it is my bad questioning that I missed out to let you know that their are some records without this format. So how to add a condition to skip those that do not fall under this format... Being a novice

      Zubair_Muhammad wrote:

      Anonymous 

      Try this custom column

       

      =Text.Start([ColumnName],Text.PositionOf([ColumnName],"/2"))
&
Text.End([ColumnName],Text.Length([ColumnName])-Text.PositionOf([ColumnName],"/",Occurrence.Last))

       

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Community Champion
        6 years ago

        Hi Anonymous 

         

        Please could you copy paste some sample rows with expected output

  • AnkitBI's avatar
    AnkitBI
    Solution 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.