Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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§orid="&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 |
Hi tried the above, i didn't face any problem.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |