get Source URL into table as custom column (json data from web)

[goober04]

hello all,

new user. super limited programming knowledge. first time on forums (advice appreciated if going about it wrong)

I have a URL query in PowerBI (https://websiteurl/api/thisisthetextiwant/01/moreurltexthere) and I want to bring the specific text highlighed into a column in my query.  the URL is the source for my table, it does not exist in its own table. I want to run future queries and the highlighted text is the only thing which is different and needs its own column. Im just lost on what type of query this would be. THe URL is always the same length and construction so my thought was how can I get the text "23 characters from the left" and until the "/01".

I had a post on the wrong forum so I am trying again. 

Table source:  (https://websiteurl/api/thisisthetextiwant/01/moreurltexthere)

Header 01 from URL json	Header 02 from URL json	Header 03 from URL json	Custom Column
data	data	data	thisisthetextiwant
data	data	data	thisisthetextiwant

[BA_Pete]

Hi @goober04 ,

To just get the required segment of the URL, you can add a new custom column like this:

Text.BeforeDelimiter(Text.Middle([url], 23), "/")

Full example query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("FcrBCYAwEATAXtLA6tdWQh4RFu5ATcitxPIl32FyTib1OIDJM1x8x4XaHTIPDxnFTz7rI2w77jbWWGYcTKX8", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [url = _t]),

    addUrlSegment = 
        Table.AddColumn(
            Source,
            "urlSegment",
            each Text.BeforeDelimiter(Text.Middle([url], 23), "/")
        )
        
in
    addUrlSegment

Example output:

Pete