Forum Discussion

KM007's avatar
KM007
Helper II
7 years ago

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&sectorid="&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