Remove Text between a pattern of string

[Anonymous]

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.

[AnkitBI]

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.

[Zubair_Muhammad]

@Anonymous 

Try this custom column

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

[Anonymous]

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]

Hi @Anonymous 

Please could you copy paste some sample rows with expected output

[Anonymous]

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]

@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])

[AnkitBI · Accepted solution]

@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.

[IDSVina]

 So I have this type of data. Could you help me write the formula that will return the values as "-00_김미자35-a3-35" for example.
I want to remove the date value. Thanks

[IDSVina]

I am new to power query. Could you please explain this formular? This is what I am looking for