How to speed up Power Query

[nthakkar]

Hello,
I am trying to fetch financial data for a list of companies website. In order to automate this, I saved the query as custom function and invoked the same when I have a list of companies. However it is taking more time to refresh. Is there anything that I can do to speed up the dataload/refresh.

Thanks.

 

[GraH - Guido]

Pictures aren't that great to share data/Query statements. Please add a sample workbook or the code like

let
step1=
step2=
in
step 2

 

[nthakkar]

Pictures aren't that great to share data/Query statements. Please add a sample workbook or the code like

let
step1=
step2=
in
step 2

Hello, Thanks for replying,
The first code is list of peer companies based on subject company entered in cell named 'ExchangeTicker'
The second code is of custom function

let
    ExchangeTicker=Excel.CurrentWorkbook(){[Name="ExchangeTicker"]}[Content]{0}[Column1],
    Source = Web.Page(Web.Contents("https://www.gurufocus.com/modules/chart/term/bubble_chart.php?term_array%5Bid%5D=67&term_array%5Bname%5D=PE+Ratio&term_array%5Bprimary_key%5D=pe&term_array%5Bkey%5D=pe&term_array%5Btreemapgroup%5D=2&term_array%5Bname_cn%5D=pe_ratio&term_array%5Bcurrent_value_key%5D=pe&term_array%5Boperating_data_category%5D=&term_array%5Bsame_operating_category_keys%5D=&no_global=true&want=table&term_id=67&term=pe&symbol="&ExchangeTicker)),
    Data0 = Source{0}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data0,{{"Header", type text}, {"Ticker", type text}, {"Company", type text}, {"Market Cap (M)", Currency.Type}, {"PE Ratio", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Ticker", "Company"}),
    #"Removed Top Rows" = Table.Skip(#"Removed Other Columns",1),
    #"Invoked Custom Function" = Table.AddColumn(#"Removed Top Rows", "financials", each financials([Ticker])),
    #"Expanded financials" = Table.ExpandTableColumn(#"Invoked Custom Function", "financials", {"Column5", "Column6", "Column7", "Column8"}, {"Column5", "Column6", "Column7", "Column8"})
in
    #"Expanded financials"

(ticker as text)=>
let
    Source = Web.Page(Web.Contents("https://markets.businessinsider.com/",
                                    [RelativePath="stocks/"&ticker&"-stock"])),
    #"Filtered Rows" = Table.SelectRows(Source, each ([ClassName] = "table table--fixed table--suppresses-line-breaks table--col-2-text-align-right table--col-3-text-align-right table--col-4-text-align-right table--col-5-text-align-right table--col-6-text-align-right")),
    #"Expanded Data" = Table.ExpandTableColumn(#"Filtered Rows", "Data", {"", "2022", "2023", "2024", "2025", "2026", "2027"}, {"Column1", "2022", "2023", "2024", "2025", "2026", "2027"}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Expanded Data"),
    #"Removed Other Columns" = Table.SelectColumns(#"Demoted Headers",{"Column5", "Column6", "Column7", "Column8"}),
    #"Filtered Rows1" = Table.SelectRows(#"Removed Other Columns", each [Column5] = "Column1" or [Column5] = "Revenue" or [Column5] = "EBITDA" or [Column5] = "Net Profit" or [Column5] = "Cash Flow per Share")
in
    #"Filtered Rows1"

 

[GraH - Guido]

How many ticker values do you return with your first Query?
The second Query runs for each row of the first Query. Then you expand data, it might be this is very expensive with heavy swapping.

You could run a query analyzer with powerBI.

Query diagnostics - Power Query

Using Query Diagnostics to diagnose what operations and data source queries are being performed

docs.microsoft.com

 

[nthakkar]

The first query may return 10 ticker values.
Will try to use query analyzer now.