Forum Discussion
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
Regardslet
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
- osama_ayoubHelper III
@Guilguis
Hi,
I used this code and I found it straightforward, you can try it
Regardslet
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"