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.

     

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi,
      Please find the table data as below:

      "/situations/i-need-to-file-my-employment-information"
"/Data/Bucket-Folder/Heritage-Bucket-Folder/2019/09/02/01/37/Cancelled-resident-withholding-tax-exemp "

       should be changed as below

      /situations/i-need-to-file-my-employment-information

      /Data/Bucket-Folder/Heritage-Bucket-Folder/Cancelled-resident-withholding-tax-exemp


      the row which doesn't start with "/Data/Bucket-Folder/Heritage-Bucket-Folder/" should be processed that the date following this text should be removed. If the row doesnt start with this text, then it should not do anything.


      Hope this data helps.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Zubair_Muhammad, i tried with the following:

        This creates a lot of duplicates after expanding the custom column.

         

        Table.AddColumn(#"Removed Top Rows", "Custom", each if Text.StartsWith([Url], "/Data/Bucket-Folder/Heritage-Bucket-Folder/") then (Text.Start([Url],Text.PositionOf([Url],"/2"))
        & Text.End([Url],Text.Length([Url])-Text.PositionOf([Url],"/",Occurrence.Last)))
        else [Url])