Timeout issue

[KM007]

Hi

I have a timeout issue see below ...

After searching for a solution I added [Timeout=#duration(0,0,15,0)]) to the query but the issue remains.

Below is my code ...

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=2&filters=0%2C1%7C%7C%7C%7C%7C%7C%7C%7C%7C%7C%7C%7C&page=1&tab=prices",[Timeout=#duration(0,0,15,0)])),
    Custom1 = Source{[ClassName="table-styled table-sortable"]}[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="table-styled table-sortable"]}[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", "Date", each DateTime.LocalNow()),
    #"Extracted Date" = Table.TransformColumns(#"Added Custom",{{"Date", DateTime.Date, type date}}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Extracted Date",{{"Index", Int64.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type3", "Unique ID", each Text.From([Index])&[Name]),
    #"Changed Type4" = Table.TransformColumnTypes(#"Added Custom1",{{"Unique ID", type text}})
in
    #"Changed Type4"

My Excel file the code above refers to contains :-

	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

[v-caliao-msft]

@KM007,

How many records in your Excel file? You can upload any Excel workbook smaller than 1 GB to Power BI. If your Excel file is larger than 1GB. You could ave it to OneDrive for Business, connect to it from Power BI, and view it in Excel Online. Please refer to the link below to see the details.
https://docs.microsoft.com/en-us/power-bi/reduce-the-size-of-an-excel-workbook

Regards,

Charlie Liao