Query error - Web.Page function didn't finish within timeout of 100 seconds

[KM007]

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

[Anonymous]

Might be worth giving the solution of this thread a try:

https://community.powerbi.com/t5/Service/The-Web-Page-function-didn-t-finish-within-timeout-of-100/t...

[KM007]

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

[Anonymous]

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.

[KM007]

Hi Ross

I added the timeout to my code and still get the exact same error dispite timeout being 10 minutes

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",[Timeout=#duration(0, 0,10, 0)])),
    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  ............

Thanks