Forum Discussion
Iterate through web url problems
Your on the right track.
I usually try using the gui then look at the advanced editor and make it into a function. Web sites can find the table or use Web by example mode. This the example mode can work better on complex sites. This is the funciton I used to get the table using by example. Though double check it has the correct data for the columns.
Notice there is a delay that gives the page time to load and it can also help slow the requests if a site stops returning data if you have too many to quick.
let MetaCritic = (page as number) as table => let Source = Web.BrowserContents("https://www.metacritic.com/browse/games/score/metascore/all/all/all?view=condensed&sort=desc&page=" & Number.ToText(page), [WaitFor=[Timeout=#duration(0, 0, 0, 0)]]), #"Extracted Table From Html" = Html.Table(Source, {{"Rating", ".small"}, {"Game", ".basic_stat:nth-last-child(3) > A:nth-child(1):nth-last-child(1)"}, {"User", ".product_avguserscore:nth-child(1) > .data"}, {"Date", ".full_release_date:nth-child(2) > .data"}}, [RowSelector=".game_product"]) in #"Extracted Table From Html" in MetaCriticlet Source = {1..3}, #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Page"}}), #"Added Custom" = Table.AddColumn(#"Renamed Columns", "MC", each MC_PageFx_4([Page])), #"Expanded MC" = Table.ExpandTableColumn(#"Added Custom", "MC", {"Date", "Game", "Rating", "User"}, {"MC.Date", "MC.Game", "MC.Rating", "MC.User"}) in #"Expanded MC"
Your on the right track.
I usually try using the gui then look at the advanced editor and make it into a function. Web sites can find the table or use Web by example mode. This the example mode can work better on complex sites. This is the funciton I used to get the table using by example. Though double check it has the correct data for the columns.
Notice there is a delay that gives the page time to load and it can also help slow the requests if a site stops returning data if you have too many to quick.
let
MetaCritic = (page as number) as table =>
let
Source = Web.BrowserContents("https://www.metacritic.com/browse/games/score/metascore/all/all/all?view=condensed&sort=desc&page=" & Number.ToText(page), [WaitFor=[Timeout=#duration(0, 0, 0, 0)]]),
#"Extracted Table From Html" = Html.Table(Source, {{"Rating", ".small"}, {"Game", ".basic_stat:nth-last-child(3) > A:nth-child(1):nth-last-child(1)"}, {"User", ".product_avguserscore:nth-child(1) > .data"}, {"Date", ".full_release_date:nth-child(2) > .data"}}, [RowSelector=".game_product"])
in
#"Extracted Table From Html"
in
MetaCritic
let
Source = {1..3},
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Page"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "MC", each MC_PageFx_4([Page])),
#"Expanded MC" = Table.ExpandTableColumn(#"Added Custom", "MC", {"Date", "Game", "Rating", "User"}, {"MC.Date", "MC.Game", "MC.Rating", "MC.User"})
in
#"Expanded MC"
I am not sure I understand all that is going on within your extract html table part, but for now I can live with that, as it works perfectly. Thank you!
Good.
I didn't write the code for the extract I just used web by example then took the code and made a function.
It trys to map text to css tags in the web page and then looks for other occurences.
This page shows how to use web by example.
https://docs.microsoft.com/en-us/power-bi/desktop-connect-to-web-by-example