Forum Discussion

stan255's avatar
stan255
New Member
1 year ago

Delay query to avoid hitting API limit

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"

 

 

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
    Omid_Motamedise
    Super User
    1 year ago

    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's avatar
      Omid_Motamedise
      Super User
      1 year ago

      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())