Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Need help to get a table from web

Hello

Well, I'm having trouble retrieving all the data from the table on this website https://mm.exame.com/maiores-empresas/. I can only pull the first page with 10 rows, and the code becomes huge, which I don't understand much. Could you help me find a solution to retrieve all the rows from the table on this website (there are only 738 rows)?

 

 

let
Fonte = Web.BrowserContents("https://mm.exame.com/maiores-empresas/"),
#"Tabela extraída de HTML" = Html.Table(Fonte, {{"Column1", "TABLE[id='table_id'] > * > TR > TH[colspan=""1""][rowspan=""1""]:nth-child(1):nth-last-child(10), TABLE[id='table_id'] > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(10)"}, {"Column2", "TABLE[id='table_id'] > * > TR > TH[colspan=""1""][rowspan=""1""]:nth-child(1):nth-last-child(10) + TH[colspan=""1""][rowspan=""1""]:nth-child(2):nth-last-child(9), TABLE[id='table_id'] > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(10) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(9)"}, {"Column3", "TABLE[id='table_id'] > * > TR > TH[colspan=""1""][rowspan=""1""]:nth-child(1):nth-last-child(10) + TH[colspan=""1""][rowspan=""1""]:nth-child(2):nth-last-child(9) + TH[colspan=""1""][rowspan=""1""]:nth-child(3):nth-last-child(8), TABLE[id='table_id'] > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(10) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(9) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(8)"}, {"Column4", "TABLE[id='table_id'] > * > TR > TH[colspan=""1""][rowspan=""1""]:nth-child(1):nth-last-child(10) + TH[colspan=""1""][rowspan=""1""]:nth-child(2):nth-last-child(9) + TH[colspan=""1""][rowspan=""1""]:nth-child(3):nth-last-child(8) + TH[colspan=""1""][rowspan=""1""]:nth-child(4):nth-last-child(7), TABLE[id='table_id'] > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(10) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(9) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(8) + TD:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(7)"}, {"Column5", "TABLE[id='table_id'] > * > TR > TH[colspan=""1""][rowspan=""1""]:nth-child(1):nth-last-child(10) + TH[colspan=""1""][rowspan=""1""]:nth-child(2):nth-last-child(9) + TH[colspan=""1""][rowspan=""1""]:nth-child(3):nth-last-child(8) + TH[colspan=""1""][rowspan=""1""]:nth-child(4):nth-last-child(7) + TH[colspan=""1""][rowspan=""1""]:nth-child(5):nth-last-child(6), TABLE[id='table_id'] > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(10) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(9) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(8) + TD:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(7) + TD:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(6)"}, {"Column6", "TABLE[id='table_id'] > * > TR > TH[colspan=""1""][rowspan=""1""]:nth-child(1):nth-last-child(10) + TH[colspan=""1""][rowspan=""1""]:nth-child(2):nth-last-child(9) + TH[colspan=""1""][rowspan=""1""]:nth-child(3):nth-last-child(8) + TH[colspan=""1""][rowspan=""1""]:nth-child(4):nth-last-child(7) + TH[colspan=""1""][rowspan=""1""]:nth-child(5):nth-last-child(6) + TH[colspan=""1""][rowspan=""1""]:nth-child(6):nth-last-child(5), TABLE[id='table_id'] > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(10) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(9) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(8) + TD:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(7) + TD:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(6) + TD:not([colspan]):not([rowspan]):nth-child(6):nth-last-child(5)"}, {"Column7", "TABLE[id='table_id'] > * > TR > TH[colspan=""1""][rowspan=""1""]:nth-child(1):nth-last-child(10) + TH[colspan=""1""][rowspan=""1""]:nth-child(2):nth-last-child(9) + TH[colspan=""1""][rowspan=""1""]:nth-child(3):nth-last-child(8) + TH[colspan=""1""][rowspan=""1""]:nth-child(4):nth-last-child(7) + TH[colspan=""1""][rowspan=""1""]:nth-child(5):nth-last-child(6) + TH[colspan=""1""][rowspan=""1""]:nth-child(6):nth-last-child(5) + TH[colspan=""1""][rowspan=""1""]:nth-child(7):nth-last-child(4), TABLE[id='table_id'] > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(10) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(9) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(8) + TD:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(7) + TD:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(6) + TD:not([colspan]):not([rowspan]):nth-child(6):nth-last-child(5) + TD:not([colspan]):not([rowspan]):nth-child(7):nth-last-child(4)"}, {"Column8", "TABLE[id='table_id'] > * > TR > TH[colspan=""1""][rowspan=""1""]:nth-child(1):nth-last-child(10) + TH[colspan=""1""][rowspan=""1""]:nth-child(2):nth-last-child(9) + TH[colspan=""1""][rowspan=""1""]:nth-child(3):nth-last-child(8) + TH[colspan=""1""][rowspan=""1""]:nth-child(4):nth-last-child(7) + TH[colspan=""1""][rowspan=""1""]:nth-child(5):nth-last-child(6) + TH[colspan=""1""][rowspan=""1""]:nth-child(6):nth-last-child(5) + TH[colspan=""1""][rowspan=""1""]:nth-child(7):nth-last-child(4) + TH[colspan=""1""][rowspan=""1""]:nth-child(8):nth-last-child(3), TABLE[id='table_id'] > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(10) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(9) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(8) + TD:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(7) + TD:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(6) + TD:not([colspan]):not([rowspan]):nth-child(6):nth-last-child(5) + TD:not([colspan]):not([rowspan]):nth-child(7):nth-last-child(4) + TD:not([colspan]):not([rowspan]):nth-child(8):nth-last-child(3)"}, {"Column9", "TABLE[id='table_id'] > * > TR > TH[colspan=""1""][rowspan=""1""]:nth-child(1):nth-last-child(10) + TH[colspan=""1""][rowspan=""1""]:nth-child(2):nth-last-child(9) + TH[colspan=""1""][rowspan=""1""]:nth-child(3):nth-last-child(8) + TH[colspan=""1""][rowspan=""1""]:nth-child(4):nth-last-child(7) + TH[colspan=""1""][rowspan=""1""]:nth-child(5):nth-last-child(6) + TH[colspan=""1""][rowspan=""1""]:nth-child(6):nth-last-child(5) + TH[colspan=""1""][rowspan=""1""]:nth-child(7):nth-last-child(4) + TH[colspan=""1""][rowspan=""1""]:nth-child(8):nth-last-child(3) + TH[colspan=""1""][rowspan=""1""]:nth-child(9):nth-last-child(2), TABLE[id='table_id'] > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(10) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(9) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(8) + TD:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(7) + TD:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(6) + TD:not([colspan]):not([rowspan]):nth-child(6):nth-last-child(5) + TD:not([colspan]):not([rowspan]):nth-child(7):nth-last-child(4) + TD:not([colspan]):not([rowspan]):nth-child(8):nth-last-child(3) + TD:not([colspan]):not([rowspan]):nth-child(9):nth-last-child(2)"}, {"Column10", "TABLE[id='table_id'] > * > TR > TH[colspan=""1""][rowspan=""1""]:nth-child(1):nth-last-child(10) + TH[colspan=""1""][rowspan=""1""]:nth-child(2):nth-last-child(9) + TH[colspan=""1""][rowspan=""1""]:nth-child(3):nth-last-child(8) + TH[colspan=""1""][rowspan=""1""]:nth-child(4):nth-last-child(7) + TH[colspan=""1""][rowspan=""1""]:nth-child(5):nth-last-child(6) + TH[colspan=""1""][rowspan=""1""]:nth-child(6):nth-last-child(5) + TH[colspan=""1""][rowspan=""1""]:nth-child(7):nth-last-child(4) + TH[colspan=""1""][rowspan=""1""]:nth-child(8):nth-last-child(3) + TH[colspan=""1""][rowspan=""1""]:nth-child(9):nth-last-child(2) + TH[colspan=""1""][rowspan=""1""]:nth-child(10):nth-last-child(1), TABLE[id='table_id'] > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(10) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(9) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(8) + TD:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(7) + TD:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(6) + TD:not([colspan]):not([rowspan]):nth-child(6):nth-last-child(5) + TD:not([colspan]):not([rowspan]):nth-child(7):nth-last-child(4) + TD:not([colspan]):not([rowspan]):nth-child(8):nth-last-child(3) + TD:not([colspan]):not([rowspan]):nth-child(9):nth-last-child(2) + TD:not([colspan]):not([rowspan]):nth-child(10):nth-last-child(1)"}}, [RowSelector="TABLE[id='table_id'] > * > TR"]),
#"Cabeçalhos Promovidos" = Table.PromoteHeaders(#"Tabela extraída de HTML", [PromoteAllScalars=true]),
#"Tipo Alterado" = Table.TransformColumnTypes(#"Cabeçalhos Promovidos",{{"Ranking das maiores", Int64.Type}, {"EMPRESA", type text}, {"CIDADE", type text}, {"ESTADO", type text}, {"SETOR", type text}, {"Receita 2021", type number}, {"Receita 2020", type number}, {"Lucro Líquido 2021", type number}, {"Ativo Total 2021", type number}, {"Patrimonio Líquido 2021", type number}})
in
#"Tipo Alterado"


 

 

  • @Guilguis

    Hi,
    I used this code and I found it straightforward, you can try it
    Regards

     

    let
    Source = Web.Page(Web.Contents("https://mm.exame.com/maiores-empresas/")),
    Data0 = Source{0}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data0,{{"Ranking das maiores", Int64.Type}, {"EMPRESA", type text}, {"CIDADE", type text}, {"ESTADO", type text}, {"SETOR", type text}, {"Receita 2021", type text}, {"Receita 2020", type text}, {"Lucro Líquido 2021", type text}, {"Ativo Total 2021", type text}, {"Patrimonio Líquido 2021", type text}})
    in
    #"Changed Type"

     

1 Reply

  • @Guilguis

    Hi,
    I used this code and I found it straightforward, you can try it
    Regards

     

    let
    Source = Web.Page(Web.Contents("https://mm.exame.com/maiores-empresas/")),
    Data0 = Source{0}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data0,{{"Ranking das maiores", Int64.Type}, {"EMPRESA", type text}, {"CIDADE", type text}, {"ESTADO", type text}, {"SETOR", type text}, {"Receita 2021", type text}, {"Receita 2020", type text}, {"Lucro Líquido 2021", type text}, {"Ativo Total 2021", type text}, {"Patrimonio Líquido 2021", type text}})
    in
    #"Changed Type"