Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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"
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
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
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())
Check out the July 2025 Power BI update to learn about new features.