Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
stan255
New Member

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"

 

 

3 REPLIES 3
Anonymous
Not applicable

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
Super User
Super User

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 

If my answer helped solve your issue, please consider marking it as the accepted solution. It helps others in the community find answers faster—and keeps the community growing stronger!
You can also check out my YouTube channel for tutorials, tips, and real-world solutions in Power Query with the following link
https://youtube.com/@omidbi?si=96Bo-ZsSwOx0Z36h

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

If my answer helped solve your issue, please consider marking it as the accepted solution. It helps others in the community find answers faster—and keeps the community growing stronger!
You can also check out my YouTube channel for tutorials, tips, and real-world solutions in Power Query with the following link
https://youtube.com/@omidbi?si=96Bo-ZsSwOx0Z36h

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.