Forum Discussion
How to extract a pathname from url text?
I have a table with a column for different urls like these
/
/person
/person?name=xyz
/person/123
/person/123?name=xyz
How can I extract it so it makes a new column like
/
/person
/person
/person
/person
So far I have this, and it removes the ? and all after that. But it doesn't handle /123. I don't know how to remove that.
= Table.AddColumn(#"Changed Type", "Table", each Text.Range([Document.request], 0, if(Text.PositionOf([Document.request], "?")=-1) then Text.Length([Document.request]) else Text.PositionOf([Document.request], "?")))
Does anyone know?
Thanks
I figured it out. First I find position of ? if exists and remove everything from it onwards.
Then I find last index of "/", and get the substring from that+1 to the end, and check if thats a number.
If it is, I get the substring from start to the above index-1, else use the above string.
6 Replies
Can you show an example? I don't understand.
Here's an even easier solution:
let
Source = Table,
#"Extracted First Characters" = Table.TransformColumns(Source, {{"Data", each Text.Start(_, 7), type text}})
in
#"Extracted First Characters"Keep in mind that this is not dynamic; it'll break down if the text length changes. This is what I meant by replacing "person" with "person/":
let
Source = Table,
#"Replaced Value" = Table.ReplaceValue(Source,"person","person/",Replacer.ReplaceText,{"Data"}),
#"Extracted Text Before Delimiter" = Table.TransformColumns(#"Replaced Value", {{"Data", each Text.BeforeDelimiter(_, "/", 1), type text}})
in
#"Extracted Text Before Delimiter"Forget about the non-digit to digit--that won't work.
--Nate
You can modify the non-digit to digit to make it work. Just use a list of split characters like "/" and "?".
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W0leK1QGSBalFxfl5yGz7vMTcVNuKyipkQX1DI2N0PpLCWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Doc = _t]), #"Split Column by Character Transition" = Table.SplitColumn(Source, "Doc", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"/", "?"}, c), {"/", "?"}), {"Doc", "Suffix"}) in #"Split Column by Character Transition"
I figured it out. First I find position of ? if exists and remove everything from it onwards.
Then I find last index of "/", and get the substring from that+1 to the end, and check if thats a number.
If it is, I get the substring from start to the above index-1, else use the above string.
