Forum Discussion

mrpowrbihelp's avatar
mrpowrbihelp
Frequent Visitor
8 years ago

scrapping amazon

I'm able to load the webpage and create a table in Power BI. Below is the advanced editor.   let Source = Web.BrowserContents("https://www.amazon.com/gp/offer-listing/0062420704"), #"Extracted Ta...
Need Help
Tutorial Requests
  • mrpowrbihelp's avatar
    mrpowrbihelp
    Frequent Visitor
    8 years ago

    Thank you! I apologize this is all new to me.

    I can get it to loop thru pages 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 and a list of Asins?

    • Stachu's avatar
      Stachu
      Community Champion
      7 years ago

      hi mrpowrbihelpty this code for replacing the query starting with PageRange 

      let
    PageRange = {0,10,20,30,40,50},
    #"Converted to Table" = Table.FromList(PageRange, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Pages"}}),
    AddASIN = Table.AddColumn(#"Renamed Columns", "ASIN", each {"aaa","bbb","ccc"}),
    #"Expanded ASIN" = Table.ExpandListColumn(AddASIN, "ASIN"),
    GetData = Table.AddColumn(#"Expanded ASIN", "Custom", each (try {_, GetData([Pages],[ASIN])} otherwise null)),
    GetTable = Table.AddColumn(GetData, "Table", each [Custom]{1}),      //this part may return error if the output of GetData doesn't have 2 parameters
    #"Expanded Table" = Table.ExpandTableColumn(GetTable, "Table", {"Seller", "Price"}, {"Seller", "Price"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Table",{"Custom"})
in
    #"Removed Columns"

      it converts list to table, and adds second column with ASIN code
      GetData in next step is using these 2 columns as input for the parameters

      • mrpowrbihelp's avatar
        mrpowrbihelp
        Frequent Visitor
        7 years ago

        Hi Stachu,

         

        Thank you for your response!

         

        I tried using your suggestion with a random ASIN. (see below). This example had 3 pages of data it should have pulled in (24 lines), but the third page of data repeated for the pages that should have been blank resulting in 36 lines of data.

         

        let
        PageRange = {0,10,20,30,40,50},
        #"Converted to Table" = Table.FromList(PageRange, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Pages"}}),
        AddASIN = Table.AddColumn(#"Renamed Columns", "ASIN", each {"B01M0GB8CC"}),
        #"Expanded ASIN" = Table.ExpandListColumn(AddASIN, "ASIN"),
        GetData = Table.AddColumn(#"Expanded ASIN", "Custom", each (try {_, GetData([Pages],[ASIN])} otherwise null)),
        GetTable = Table.AddColumn(GetData, "Table", each [Custom]{1}), //this part may return error if the output of GetData doesn't have 2 parameters
        #"Expanded Table" = Table.ExpandTableColumn(GetTable, "Table", {"Seller", "Price"}, {"Seller", "Price"}),
        #"Removed Columns" = Table.RemoveColumns(#"Expanded Table",{"Custom"})
        in
        #"Removed Columns"

         

        Also, can I bring in an excel file with a list of ASIN's in stead of how you have "AddASIN = Table.AddColumn(#"Renamed Columns", "ASIN", each {"B01M0GB8CC"})," to list each one?

         

        Thank you!