Help with M query hanging

[KM007]

Hi

My query hangs while running and I cannot figure out why - please help! Much appreciated

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=0&page=1&tab=prices")),
    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("https://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 = #"SectorIDs pt1",
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SectorID", type text}}),
    // Call function and cleanup
    CallEachFunction = Table.AddColumn(#"Changed Type", "WebCall", each WebCall([SectorID])),
    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",{{"Column1", "Page no."}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns1",{"Name2", "Yield", "More information", "Go to"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns",{{"Current price Sell (p)", "Sell (p)"}, {"Current price Buy (p)", "Buy (p)"}, {"Current price Change (p)", "Change (p)"}}),
    #"Added Custom2" = Table.AddColumn(#"Renamed Columns2", "Custom", each [Name] & " - Unbundled"),
    #"Renamed Columns3" = Table.RenameColumns(#"Added Custom2",{{"Custom", "Name+"}})
in
    #"Renamed Columns3"

SectorIDs pt1 table is below

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
133	Latin America
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
104	UK Equity & Bond Income
103	UK Equity Income
127	UK Gilt
135	UK Index Linked Gilt
118	UK Smaller Companies
48	Unclassified
157	Volatility Managed

[Interkoubess]

Hi @KM007,

The error I have got is below:

Expression.Error: The name 'SectorIDs pt1' wasn't recognized.  Make sure it's spelled correctly.

Do you really have 

'SectorIDs pt1'

Could you please explain what you want to do and we can figure out another approach...

Ninter

[KM007]

Hi @Interkoubess

What the M query is doing is scrpaing the HL.co.uk site of all funds and prices.

Site address is ....

https://www.hl.co.uk/funds/fund-discounts,-prices--and--factsheets/search-results?sectorid=121&lo=0&...

... the page is scraped. At each 'sectorid=' the value is changed for next value in SectorIDs table.

For the SectorIDs table I copied the values from the page source (see below)

<div class="large-5 medium-5 columns end padding-left-none medium-padding-left medium-padding-right-none"><div class="select_wrap"><select id="search-sector" name="sectorid" class="default"><option value="">Search by sector</option>
<option value="121">Asia Pacific Ex Japan</option>
<option value="114">Asia Pacific Inc Japan</option>
<option value="155">China/ Greater China</option>
<option value="111">Europe Excluding UK</option>
<option value="102">Europe Including UK</option>
<option value="117">European Smaller Companies</option>
<option value="115">Flexible Investment</option>
<option value="150">GBP Corporate Bond</option>
<option value="151">GBP High Yield</option>
<option value="152">GBP Strategic Bond</option>
<option value="109">Global</option>
<option value="106">Global Bonds</option>
<option value="112">Global Emerging Markets</option>
<option value="156">Global Emerging Markets Bond</option>
<option value="139">Global Equity Income</option>
<option value="126">Guaranteed/Protected</option>
<option value="113">Japan</option>
<option value="132">Japanese Smaller Companies</option>
<option value="133">Latin America</option>
<option value="137">Mixed Investment 0-35% Shares</option>
<option value="131">Mixed Investment 20-60% Shares</option>
<option value="107">Mixed Investment 40-85% Shares</option>
<option value="123">Money Market</option>
<option value="999">N/A</option>
<option value="110">North America</option>
<option value="130">North American Smaller Cos</option>
<option value="71">Offshore</option>
<option value="400">Pension Trusts</option>
<option value="108">Property</option>
<option value="138">Short Term Money Market</option>
<option value="129">Specialist</option>
<option value="136">Targeted Absolute Return</option>
<option value="134">Technology & Telecoms</option>
<option value="101">UK All Companies</option>
<option value="104">UK Equity & Bond Income</option>
<option value="103">UK Equity Income</option>
<option value="127">UK Gilt</option>
<option value="135">UK Index Linked Gilt</option>
<option value="118">UK Smaller Companies</option>
<option value="48">Unclassified</option>
<option value="157">Volatility Managed</option>
</select></div></div>

I hope this all makes sense.

While I run my code it hangs frequently. If you can help it would be very much appreciated

Thanks!

[KM007]

Hi

Please may anyone help me out. I'd very much appreciate it

Thanks

[Interkoubess]

Hi @KM007,

I did not understand very well what you are trying to do.

Please could you explain with your expected results and I will try to help or call experts of M to help you.

Ninter

[KM007]

Hi @Interkoubess

See pic below

For each sector (smaller red box) in the list eg 'Asia Pacific Ex Japan' I want to download the Funds Listed (bigger red box)

Does this help you understand what I want to achieve?

Much appreciate your help!

Thanks