SOLVED

Get an extract of the source web address to appear in a power query

Brass Contributor

Hi,

i would very much appreciate if anyone can help with something that i thought would have been possible but i am having no luck achieving it.

i run a power query that gets data from the web from the site https://en.wikipedia.org/wiki/London

and i am trying to get the "london" part of that web adress to appear as a column in the same power query results.

 

i know if i can get the address "https://en.wikipedia.org/wiki/London" , as a column in my power query, then i will know how be able to extract the "london" part by using split columns etc.

 

its just i cant get any of the actual source address text (above) and cant put it into the power query anywhere. Is this possible ??

 

i have attached my document , which is just a working doc of what im trying to achieve.

so i have a power query that gets the data from the web address of https://en.wikipedia.org/wiki/London

i have the web address in cell L1 and i have gived it a named reference of Address04

my source code for my power query

let
getAddress04 = Excel.CurrentWorkbook(){[Name="Address04"]}[Content]{0}[Column1],
Source = Web.Page(Web.Contents(getAddress04, [Timeout=#duration(0, 0, 3, 0)])),

Data6 = Source{6}[Data],
#"Changed Type" = Table.TransformColumnTypes(Data6,{{"Header", type text}, {"Country of birth", type text}, {"Population", Int64.Type}})
in
#"Changed Type"

 

Really hoping that this is possible and someone can help me out with this, it would be very much appreciated, with many thanks, steve r

5 Replies

@stever78 

 

I HAD ATTACHED THE WRONG DOCUMENT, PLS FIND CORRECT EXCEL FILE

best response confirmed by stever78 (Brass Contributor)
Solution

@stever78 

You may extract city from URL as

city = Text.AfterDelimiter( getAddress04, "/", {0, RelativePosition.FromEnd} )

and add the value to custom column

thanks for reply sergie.

should i just open up the advanced editor in the power query and put your code in the next line at the bottom ?
Ive done it - thanks so much for you help segie !!!!!

@stever78 , you are welcome.

Yes, that's with advanced editor.

1 best response

Accepted Solutions
best response confirmed by stever78 (Brass Contributor)
Solution

@stever78 

You may extract city from URL as

city = Text.AfterDelimiter( getAddress04, "/", {0, RelativePosition.FromEnd} )

and add the value to custom column

View solution in original post