Query timeout

[KM007]

Hi

Having difficulty getting query to work. Keep getting timeout error. Any suggestions will be very helpful.

Please see code below.

let

// Function for each Sector-Information
WebCall = (SectorID as text) =>
let
    Source = Web.Page(Web.Contents("http://www.hl.co.uk/funds/fund-discounts,-prices--and--factsheets/search-results?sectorid="&SectorID&"&lo=0&page=1&tab=prices",[Timeout=#duration(0, 0, 5, 0)])),
    Custom1 = Source{[ClassName="sortableTable"]}[Data],
    ListOfAllPages = if List.Count(List.Skip(Text.Split(Custom1{0}[Name], "#(lf)")))=0 then {"1"} else List.Skip(Text.Split(Custom1{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("http://www.hl.co.uk/funds/fund-discounts,-prices--and--factsheets/search-results?tab=prices&sectorid="&SectorID&"&lo=0&page="&[Column1]&"")){[ClassName="sortableTable"]}[Data]),
    #"Expanded Custom" = Table.ExpandTableColumn(RearragnedURL, "Custom", {"Name", "Name2", "Current price Sell (p)", "Current price Buy (p)", "Current price Change (p)", "Yield", "More information", "Go to"}, {"Name", "Name2", "Current price Sell (p)", "Current price Buy (p)", "Current price Change (p)", "Yield", "More information", "Go to"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each not Text.StartsWith([Name], "Page:")),
    #"Changed Type" = try Table.TransformColumnTypes(#"Filtered Rows",{{"Name", type text}, {"Name2", type text}, {"Current price Sell (p)", type number}, {"Current price Buy (p)", type number}, {"Current price Change (p)", type number}, {"Yield", Percentage.Type}, {"More information", type text}, {"Go to", type any}}) otherwise null
in
   try #"Changed Type" otherwise null,

// Start of main query: Dig into Html to retrieve a table with all Sectors 
    Source = Excel.Workbook(File.Contents("C:\Users\PC\Google Drive\Finance Docs\HL\HL Sector IDs.xlsm"), null, true),
    #"HL Sector IDs_Sheet" = Source{[Item="HL Sector IDs",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(#"HL Sector IDs_Sheet",{{"Column1", Int64.Type}, {"Column2", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column1] <> null)),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Column1", "Custom.1"}, {"Column2", "Sector"}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"Custom.1", type text}}),
   
// Call function and cleanup
    CallEachFunction = Table.AddColumn(#"Changed Type2", "WebCall", each WebCall([Custom.1])),
    FilterOutEmpties = Table.SelectRows(CallEachFunction, each ([WebCall] <> null)),
    ExpandColumns = Table.ExpandTableColumn(FilterOutEmpties, "WebCall", {"Column1", "Name", "Name2", "Current price Sell (p)", "Current price Buy (p)", "Current price Change (p)", "Yield", "More information", "Go to"}, {"Column1", "Name", "Name2", "Current price Sell (p)", "Current price Buy (p)", "Current price Change (p)", "Yield", "More information", "Go to"}),
    #"Changed Type1" = Table.TransformColumnTypes(ExpandColumns,{{"Current price Sell (p)", type number}, {"Current price Buy (p)", type number}, {"Current price Change (p)", type number}, {"Yield", type number}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"Custom.1", "SectorID"}, {"Column1", "Page no."}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns1",{"Name2", "Yield", "More information", "Go to"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1),
    #"Renamed Columns2" = Table.RenameColumns(#"Added Index",{{"Current price Sell (p)", "Sell (p)"}, {"Current price Buy (p)", "Buy (p)"}, {"Current price Change (p)", "Change (p)"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns2", "Custom", each [Name] & " - Unbundled"),
    #"Renamed Columns3" = Table.RenameColumns(#"Added Custom",{{"Custom", "Name+"}})
in
    #"Renamed Columns3"

Sector ID table below ...

Column1Column2

null	Search by sector
121	Asia Pacific Ex Japan
114	Asia Pacific Inc Japan
155	China/ Greater China
111	Europe Excluding UK
102	Europe Including UK
117	European Smaller Companies
115	Flexible Investment
150	GBP Corporate Bond
151	GBP High Yield
152	GBP Strategic Bond
109	Global
106	Global Bonds
112	Global Emerging Markets
156	Global Emerging Markets Bond
139	Global Equity Income
126	Guaranteed/Protected
113	Japan
132	Japanese Smaller Companies
137	Mixed Investment 0-35% Shares
131	Mixed Investment 20-60% Shares
107	Mixed Investment 40-85% Shares
123	Money Market
999	N/A
110	North America
130	North American Smaller Cos
71	Offshore
400	Pension Trusts
108	Property
138	Short Term Money Market
129	Specialist
136	Targeted Absolute Return
134	Technology & Telecoms
101	UK All Companies
105	UK Corporate Bond
104	UK Equity & Bond Income
103	UK Equity Income
127	UK Gilt
135	UK Index Linked Gilt
118	UK Smaller Companies
125	Unauthorised
48	Unclassified

[rocky09]

Hi tried the above, i didn't face any problem.

[KM007]

Ah! Thanks @rocky09

Could it be my broadband speed?

I wonder

[rocky09]

@KM007

might be..!!

My current speed is 36 Mbps.

[KM007]

Hmmm! Could be then as mine is something daft like 3Mbps

Thanks for your time @rocky09