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 Table From Html" = Html.Table(Source, {{"Seller Name", ".olpSellerName"}, {"Seller Price", ".a-color-price"}, {"Condition", ".olpConditionColumn > .a-section"}, {"Ships From", ".a-unordered-list.olpFastTrack > :nth-child(2):nth-last-child(2)"}}, [RowSelector=".olpOffer"]),
#"Changed Type" = Table.TransformColumnTypes(#"Extracted Table From Html",{{"Seller Name", type text}, {"Seller Price", Currency.Type}, {"Condition", type text}, {"Ships From", type text}})
in
#"Changed Type"

 

I want to be able to have a table of ASINs that are at the end of the website address (highlighted in red) so i can scrap and log multiple products information each week. I will also need to scrap page 1,2,3, ect depending on how many there are.

 

But to start...I've watched videos to add a line at the top of the code

=(ASIN as text) as table=>

and change the website address to..Source = Web.BrowserContents("https://www.amazon.com/gp/offer-listing/"&ASIN"),

 

but I keep getting a Token Literal expected error.

What is going wrong? 

Need Help
Tutorial Requests

7 Replies

  • Stachu's avatar
    Stachu
    Community Champion
    8 years ago

    my guess is unnecessary paranthesis, try this

    Source = Web.BrowserContents("https://www.amazon.com/gp/offer-listing/" & ASIN),
    • 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