Speed up Power BI query

[KM007]

Hi

Please can anyone help me to speed up my Power BI query?

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")),
    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", type text}, {"Column2", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column1] <> null)),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Column1", "Custom.1"}, {"Column2", "Sector"}}),
    // Call function and cleanup
    CallEachFunction = Table.AddColumn(#"Renamed Columns", "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"}),
    #"Removed Columns" = Table.RemoveColumns(ExpandColumns,{"Custom.1", "Column1", "Name2", "Yield", "More information", "Go to"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each [Name] & " - Unbundled"),
    #"Renamed Columns3" = Table.RenameColumns(#"Added Custom",{{"Custom", "Name+"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns3",{{"Current price Sell (p)", type number}, {"Current price Buy (p)", type number}, {"Current price Change (p)", type number}}),
    #"Renamed Columns2" = Table.RenameColumns(#"Changed Type1",{{"Current price Sell (p)", "Sell (p)"}, {"Current price Buy (p)", "Buy (p)"}, {"Current price Change (p)", "Change (p)"}})
in
    #"Renamed Columns2"

Much appreciated

[v-chuncz-msft]

@KM007,

The following article lists some useful tips.

https://www.thebiccountant.com/speedperformance-aspects/