How to extract a pathname from url text?

[TheInvoker]

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

[TheInvoker · Accepted solution]

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.

[Anonymous]

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

[AlexisOlson]

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"

[Anonymous]

You could also replace values and replace "person" with "person/" and then split by the second "/".

--Nate

[TheInvoker]

Can you show an example? I don't understand.

[Anonymous]

You can use the Split by Character Transition under the Split menu.

--Nate