• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to speed up Power Query

nthakkar

New Member
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.
 

Attachments

  • company list.png
    company list.png
    72.2 KB · Views: 9
  • Web query custom function.png
    Web query custom function.png
    61.9 KB · Views: 8
Pictures aren't that great to share data/Query statements. Please add a sample workbook or the code like
Code:
let
step1=
step2=
in
step 2
 
Pictures aren't that great to share data/Query statements. Please add a sample workbook or the code like
Code:
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


Code:
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"


Code:
(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"
 
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.
 
Back
Top