Dynamic connection to Web URL

[Anonymous]

Hi

I am connecting to a csv file at the following URL with a standard "web" connection  : https://www.at.govt.nz/media/1975723/patronage-data-dec-2017.csv

The connection works fine, and I can extract the data.  However the structure of the URL includes a Month (dec) , and Year (2017) reference.  I would expect next month the URL I want to connect to  will be updated to :

https://www.at.govt.nz/media/1975723/patronage-data-jan-2018.csv

I have tried using * and ? wildcards to the Year and Month "URL parts" within the Advanced settings component of the web connection, without any luck (see below)- the connection fails.

Can anyone suggest a way to connect to dynamic URLs ?  

Cheers Steve

[Anonymous · Accepted solution]

@Anonymous,

Add a new blank query in Power BI Desktop, then paste the following code to the Advanced Editor of the blank query.

(Month as text,year as number)=>

let
 Source = Csv.Document(Web.Contents("https://www.at.govt.nz/media/1975723/patronage-data-" & ""&Month&"" & "-" & ""&Number.ToText(year)&"" & ".csv"),[Delimiter=",", Columns=8, Encoding=1252, QuoteStyle=QuoteStyle.None]),
 #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}})
in
 #"Changed Type"

Input month and year in the function and invoke the function to get required table.


Regards,
Lydia

[Anonymous]

Thanks for this Lydia.

Out of interest do you know if there is any way to avoid needing user input? Ie extract any URL   that has a 3 character month and 4 character year, followed by a CSV suffix?    

https://www.at.govt.nz/media/1975723/patronage-data-???-????.csv

Cheers

Steve

[Anonymous]

@Anonymous,

Another method is to create two parameters(Month and year) listing all the possible values, then users are able to choose values to return different queries.

let
 Source = Csv.Document(Web.Contents("https://www.at.govt.nz/media/1975723/patronage-data-" & ""&Month&"" & "-" & ""&year&"" & ".csv"),[Delimiter=",", Columns=8, Encoding=1252, QuoteStyle=QuoteStyle.None])
in
    Source

Regards,
Lydia

[Farhad88]

Hi Lydia,

Thanks for providing the solution. I need to cover a little different scenario as the following (screenshot attached):

My client wants to select the value of the parameter using a slicer. 

As you know, it is fairly easy to do so when using DirectQuery on SQL data source, but I cannot use the same solution for a Rest API data source (because the value of the parameter would be always the same as its defualt value!). 

Do you think there might be any solution to this issue?

Thanks and best regards,

Hossein

[mrpowrbihelp]

@Anonymous

I'm trying to scrape Amazon and go thru a list of ASINs and an unknown amount of pages per ASIN.

I've tried the multiple parameters option, which works, but I don't have a set number of pages that I know for each instance so I would end up with a million rows and have to delete duplicates.

I've tried another approach that i can get to loop thru all pages without knowing the page number using..

https://www.mattmasson.com/2014/11/iterating-over-an-unknown-number-of-pages-in-power-query/

with my Code looking like..

(Page as number) as table =>

let
Source = Web.BrowserContents("https://www.amazon.com/gp/offer-listing/B01MQWUXZS/ref=olp_page_next?ie=UTF8&f_all=true&startIndex=" & Number.ToText(Page)),
#"Extracted Table From Html" = Html.Table(Source, {{"Seller", ".olpSellerName"}, {"Price", ".a-color-price"}}, [RowSelector=".olpOffer"])
in
#"Extracted Table From Html"

let
PageRange = {0,10,20,30,40,50},
Source = List.Transform(PageRange, each try {_, GetData(_)} otherwise null),
First = List.FirstN(Source, each _ <> null),
Table = Table.FromRows(First, {"Page", "Column1"}),
Expanded = Table.ExpandTableColumn(Table, "Column1", {"Seller", "Price"}, {"Seller", "Price"})
in
Expanded

BUT I'm having trouble combining the two,  I need it to also go thru all the different ASINs and I want my code to look more like

(Page as number, Asin as text) as table =>

let
Source = Web.BrowserContents("https://www.amazon.com/gp/offer-listing/" & Asin & "/ref=olp_page_next?ie=UTF8&f_all=true&startIndex=" & Number.ToText(Page)),
#"Extracted Table From Html" = Html.Table(Source, {{"Seller", ".olpSellerName"}, {"Price", ".a-color-price"}}, [RowSelector=".olpOffer"])
in
#"Extracted Table From Html"

How do I adjust the next Query to look at a list of Page numbers (that can go to a crazy amount -but will stop once it hits a error) and a list of Asins at the same time?