Delay query to avoid hitting API limit

[stan255]

How can I add a delay between my queries to avoid hitting API limit? The API I'm querying limits me to 75 per minute. However, I have 500 queries to execute

Query

= (Stock as text) as table => 
let
    Source = Json.Document(Web.Contents("https://www.alphavantage.co/query?function=TIME_SERIES_DAILY_ADJUSTED&outputsize=full&apikey=X&symbol="&Stock)),
    #"Time Series (Daily)" = Source[#"Time Series (Daily)"],
    #"Converted to Table" = Record.ToTable(#"Time Series (Daily)"),
    #"Expanded Value" = Table.ExpandRecordColumn(#"Converted to Table", "Value", {"1. open", "2. high", "3. low", "4. close"}, {"1. open", "2. high", "3. low", "4. close"})
in
    #"Expanded Value"

Then I'm invoking the query via a custom function which includes a list of stocks

let
    Source = #table(
        type table [#"Stock Name" = text], 
            {
                {"MMM"},{"AOS"},{"ABT"},{"ABBV"},{"ACN"},{"ADBE"},{"AMD"},{"AES"},{"AFL"},{"A"},{"APD"},{"ABNB"},{"AKAM"},{"ALB"},{"ARE"},{"ALGN"},{"ALLE"},{"LNT"},{"ALL"},{"GOOGL"},{"GOOG"},{"MO"},{"AMZN"},{"AMCR"},{"AMTM"},{"AEE"},{"AEP"},{"AXP"},{"AIG"},{"AMT"},{"AWK"},{"AMP"},{"AME"},{"AMGN"},{"APH"},{"ADI"},{"ANSS"},{"AON"},{"APA"},{"AAPL"},{"AMAT"},{"APTV"},{"ACGL"},{"ADM"},{"ANET"},{"AJG"},{"AIZ"},{"T"},{"ATO"},{"ADSK"},{"ADP"},{"AZO"},{"AVB"},{"AVY"},{"AXON"}
            }
    ),
    #"Invoked Custom Function" = Table.AddColumn(Source, "Stock Table", each #"Price Data"([Stock Name])),
    #"Expanded Stock Table" = Table.ExpandTableColumn(#"Invoked Custom Function", "Stock Table", {"Name", "Open", "High", "Low", "Adj Close", "Volume"}, {"Name", "Open", "High", "Low", "Adj Close", "Volume"})
in
    #"Expanded Stock Table"

[Anonymous]

Hi @stan255 ,
You can try using the Function.InvokeAfter() function when you make a query; this delay helps ensure that you don't exceed the API limit of 75 queries per minute. Specifically, replace #“Invoked Custom Function” with the following code

AddDelay = Table.AddColumn(Source, "Stock Table", each Function.InvokeAfter(() => #"Price Data"([Stock Name]), #duration(0, 0, 0, 1)))

Function.InvokeAfter - PowerQuery M | Microsoft Learn

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Omid_Motamedise]

You can add a finite loop by List.Generate to run for the time you need to pause query.

but became full as power query is lazy, it can be neglected, so add this as midd step of your calculation 

[Omid_Motamedise]

You can use the below steps for example for a minute pause. (I use mobile so beware full about the spelling of functions)

T=DateTime.LocalNow(),

Timeafterpause=T+#duration(0,0,1,0),

Loop= List.Generate(()=>T, each _<Timeafterpause, each DateTime.LocalNow())