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
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!

  • ImkeF's avatar
    ImkeF
    Community Champion
    7 years ago

    Hi mrpowrbihelp,

     

    I've combined the code into one query: 

     

    let

fnScrapeASIN = (ASIN as text) => 
 List.Generate( 
                () => [Table = null, page = 10, PrevTable = 0],
                each [Table] <> [PrevTable],
                each [Source = Web.BrowserContents("https://www.amazon.com/gp/offer-listing/" & ASIN & "/ref=olp_page_13?ie=UTF8&f_all=true&startIndex=" & Text.From(page)),
                    Table = 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"]),
                    PrevTable = [Table],
                    page = [page]+10],
                    each [Table]
                    ),

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQzNDUxNzA2MVGK1YlWMjA3NjWztDA2jVCKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ASIN = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ASIN", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Skip(fnScrapeASIN([ASIN]),1)),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Expanded Custom1" = Table.ExpandTableColumn(#"Expanded Custom", "Custom", {"Seller Name", "Seller Price", "Condition", "Ships From"}, {"Seller Name", "Seller Price", "Condition", "Ships From"})
in
    #"Expanded Custom1"

    All you have to do is to replace the bold part in the Source-step by a reference to a table with your ASIN-numbers (column named "ASIN").

     

    This is a recursive query that retrieves the next page (+10) if the result of the current request is not equal to the result of the previous request (as the page seems to always return the last page, even if the numbers go up)