Forum Discussion
Cleansing URL paths - stuck
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?
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"
3 Replies
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"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?
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]