Cleansing URL paths - stuck

[Anonymous]

I have spreadsheet that has an assortmant of URLs in one column.  I've tried to split the column by the / but the length of the URLs varies so I can't just elelminate the parts I don't want. I only want the highlighted areas, all in one column if possible, how do I get that result?

[Anonymous]

Hi Thanks for the suggestion. It solved part of my problem, but I still have the various numbers hanging out in some of the strings. Ultimately I need to know how many say Appraisal, or AppraisalSearch so I still need to get rid of the numbers. So frustrated, I wish I knew how to do these things. Any suggestions?

[Vijay_A_Verma]

Use this in a custom column

[a = List.LastN(Text.Split([Text], "/"), 3),
b = if Text.StartsWith(a{2}, "?") then List.RemoveLastN(a, 1) else if a{1} = "#" then a else List.RemoveFirstN(a, 1), result = if Value.FromText(b{1}) is number then b{0} else Text.Combine(b, "/")][result]

[Vijay_A_Verma · Accepted solution]

Use this in a custom column where [Text] should be replaced with your column name

[a = List.LastN(Text.Split([Text], "/"), 3),
b = if Text.StartsWith(a{2}, "?") then List.RemoveLastN(a, 1) else if a{1} = "#" then a else List.RemoveFirstN(a, 1), result = Text.Combine(b, "/")][result]

A sample complete code for testing

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WStZX1nfWD9aP0vfU91eK1UGI2BfaJiUmgYX0g1IL8otKioEyUBYOYX3H5OT80rySzLx0pdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Text = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each [a = List.LastN(Text.Split([Text], "/"), 3),
b = if Text.StartsWith(a{2}, "?") then List.RemoveLastN(a, 1) else if a{1} = "#" then a else List.RemoveFirstN(a, 1), result = Text.Combine(b, "/")][result])
in
    #"Added Custom"