Forum Discussion
KM007
7 years agoHelper II
Query error - Web.Page function didn't finish within timeout of 100 seconds
Hi
I am having difficulty running my query. It seems to stall / hang and I get error "Web.Page function didn't finish within timeout of 100 seconds"
Below is my code...
let
// Function for each Sector-Information
WebCall = (SectorID as text) =>
let
Source = Web.Page(Web.Contents("https://www.hl.co.uk/funds/fund-discounts,-prices--and--factsheets/search-results?sectorid="&SectorID&"&lo=2&filters=0%2C1%7C%7C%7C%7C%7C%7C%7C%7C%7C%7C%7C%7C&page=1&tab=prices")),
Data = Source{[ClassName="sortableTable"]}[Data],
ListOfAllPages = if List.Count(List.Skip(Text.Split(Data{0}[Name], "#(lf)")))=0 then {"1"} else List.Skip(Text.Split(Data{0}[Name], "#(lf)")),
#"Converted to Table" = Table.FromList(ListOfAllPages, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
RearragnedURL = Table.AddColumn(#"Converted to Table", "Custom", each Web.Page(Web.Contents("https://www.hl.co.uk/funds/fund-discounts,-prices--and--factsheets/search-results?tab=prices&filters=0%2C1%7C%7C%7C%7C%7C%7C%7C%7C%7C%7C%7C%7C§orid="&SectorID&"&lo=2&page="&[Column1]&"")){[ClassName="sortableTable"]}[Data]),
#"Expanded Custom" = Table.ExpandTableColumn(RearragnedURL, "Custom", {"Name", "Current price Sell (p)", "Current price Change (p)", "More information"}, {"Name", "Sell (p)", "Change (p)", "More information"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Expanded Custom", "More information", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), {"More information.1", "More information.2", "More information.3", "More information.4", "More information.5", "More information.6"}),
#"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"More information.2", "Unit type"}, {"More information.4", "Sector"}, {"More information.6", "Valuation date"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"More information.1", "More information.3", "More information.5", "Column1"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns", "Name + type", each [Name]&" - "&[Unit type]),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Name", "Name + type", "Sell (p)", "Change (p)", "Unit type", "Sector", "Valuation date"}),
#"Filtered Rows" = Table.SelectRows(#"Reordered Columns", each not Text.StartsWith([Name], "Page:")),
#"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Name", "Unit type"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns1",{{"Name + type", type text}, {"Sell (p)", type number}, {"Change (p)", type number}, {"Sector", type text}, {"Valuation date", type date}})
in
#"Changed Type",
// Start of main query: Dig into Html to retrieve a table with all Sectors
Source = #"SectorIDs - 1",
// Call function and cleanup
CallEachFunction = Table.AddColumn(Source, "WebCall", each WebCall([SectorID])),
FilterOutEmpties = Table.SelectRows(CallEachFunction, each [WebCall] <> null),
#"Expanded WebCall" = Table.ExpandTableColumn(FilterOutEmpties, "WebCall", {"Name + type", "Sell (p)", "Change (p)", "Sector", "Valuation date"}, {"Name + type", "Sell (p)", "Change (p)", "Sector", "Valuation date"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded WebCall",{"SectorID", "SectorName"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Name + type", type text}, {"Sell (p)", type number}, {"Change (p)", type number}, {"Sector", type text}, {"Valuation date", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each (not Number.IsNaN([#"Sell (p)"])))
in
#"Filtered Rows"
Source = #"SectorIDs - 1" =
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZTbbuIwEIZfxULauyKcE4dLumLZbksXLXSlqu2FSYbEqmOztrOCt6+HOBBSepn55vBn5k9eXnpBGPRuelPDGVmylG95SmZ78ovtmOy93TgexF1+J9N2QpK4hO8Fl2xA5hqYBU2Oj74e+88qrXbgOqeiyrjMydN9TWl4pq5vhwajE2WSrEomBDZXpRvOwfgknP9DwJ5vBDb5D8aWIK1XRx2d3y5dld4p7dSRWyUzDwMPf/K8IM8cRANCD1YWS3L31ucqOkEo1IYJHxieAse0Rlh4Ds9K0Dm+2oLpd7A+Ixl+ndEaGE1aaf8qbg+4K1VCjcNjl4ppJi1ANlhqZSG14KuDyOHWvaKweQYDXy01wqIHZrkkUyeMp/6aEV5kwfeQtTZNaD9KvpFVwfSpPriWF9L+kF4m0qsNY9ofdzqGqGihJBz8ho7hyQR38ziY+nfFaz8qbYuO7E/xtp3qCSOU/Hu7NYXS9WZjimVLkIYrSda6Ms3h6BgB+lLbgx+BoZWrtWQNuiSfpAYhSl3tIOVMcOODER5vzXQO7mBkujFKVM6jf8BWujkYfoFrSAuphMoP5LWiNBy6KQKcCRpFqP7pnkyF6N6SxjXy1vHlaK8LG9HoIu3CYaMazbloZCd15E5msCcPXL479WccjGt83V3xkbqvnRnj/iiNUROc8lcJ5zqBChZMshzh2wc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [SectorID = _t, SectorName = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"SectorID", type text}, {"SectorName", type text}}),
#"Removed Bottom Rows" = Table.RemoveLastN(#"Changed Type",29)
in
#"Removed Bottom Rows"
I will appreciate any guidance, help, advice. Thank you so much in advance
4 Replies
- AnonymousNot applicable
Might be worth giving the solution of this thread a try:
- KM007Helper II
Hi Ross
I don't think time is the issue, I think something else is going on but I don't know what. It seems to get to a certain point and just stop.
Thanks for your time
- AnonymousNot applicable
My suggestion is really to apply the timeout value to your Web.Page and extend the timeout setting to something much larger. This will rule out whether the timeout is causing your query to simply stop prematurely.