Forum Discussion

goober04's avatar
goober04
New Member
2 years ago

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

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 jsonHeader 02 from URL jsonHeader 03 from URL jsonCustom Column
datadatadatathisisthetextiwant
datadatadatathisisthetextiwant
Need Help

1 Reply

  • BA_Pete's avatar
    BA_Pete
    Super User
    2 years ago

    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