Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Dynamic connection to Web URL

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 extra...
  • Anonymous's avatar
    Anonymous
    8 years ago

    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's avatar
Anonymous
Not applicable
8 years ago

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's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      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

      • mrpowrbihelp's avatar
        mrpowrbihelp
        Frequent Visitor
        8 years ago

        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?