first time user power query extracting text from URL into column

[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".

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

[goober04]

Ok. we are getting somewhere but still an error. 

the URL isnt in the dataset itself, its where the dataset it coming from. Am i missing something....again?

[eliasayyy]

@goober04  heres my result

[goober04]

So what you are doing I understand. My challenge is I have no existing table with the URL. The URL text I am wanting is my source URL where I am then extracting table data from. The table data itself does not have the URL available (unless theres a way to do that). See attached image of the full URL for my source. From my applied steps, the goal was to extract the URL text and attach it to every record value in the table. Make sense?

[eliasayyy]

@goober04  heres my result

[eliasayyy]

the name of my column i used was [url] you need to change "url" with your column name containing the url and make sure its case sensitive

[eliasayyy]

what is your url column called?

[goober04]

I called it "database"

[eliasayyy]

@goober04  i meant which column has the urls you need to extract the tet from?

[eliasayyy]

hello @goober04  in power query go to add columns and choose new custom column 
type in 

let
    fullURL = [url],  // Get the full URL from the current row
    extractedText = Text.Middle(fullURL, 23, Text.PositionOf(fullURL, "/01") - 23)  // Extract the desired substring
in
    extractedText

[goober04]

Appreciate the quick response @eliasayyy . Tried it and this is the result. What am I missing here?

[eliasayyy]

try this 

let
    fullURL = [url],  // Get the full URL from the current row
    extractedText = Text.Middle(fullURL, 23, Text.PositionOf(fullURL, "/01") - 23)  // Extract the desired substring
in
    extractedText