Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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())
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
11 | |
8 | |
8 | |
7 |
User | Count |
---|---|
15 | |
13 | |
9 | |
6 | |
6 |