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.
Greetings,
Key points of my help request: PowerQuery, Loops, List.Generate, Function.InvokeAfter, Formatting error returned
I need to query an API that allows only 200 queries per minute. Then I need to stop querying it for a full minute before being allowed to query it 200 more times.
I have tried implementing a conditional delay, but I keep not getting it right : delay wouldn't launch, then loop would run indefinitely, and now I am being told that PQuery cannot transform a record data into a logical data. Except what I want is the record I get from compiling all the iterations of my custom function GetTeamleaderInvoicesDetails (which works just fine on a set of data that doesn't meet the rate limit of 200 and I haven't figured out how to use rate-limiting yet so this is the next best thing).
Here is the M code for my query :
Solved! Go to Solution.
Unfortunately, I was not able to make it work. But I eventually found an alternative :
let
SecondsWaiting = 65,// 60 seconds wait time between two bunches of 200 queries needed, 65 to be safe
WaitTime = #duration(0,0,0,SecondsWaiting),
CallWait = 195, // max 200 API calls per minute, set to 195 to be safe
// Call GetTL function to generate the list of IDs
Source = List.Generate(() =>
// Start loop
[Result =
try GetTLInvoices(1)
otherwise null,
VarPage = 1
],
// Call API until no result get turned up
each [Result]<>null,
each [Result=
try GetTLInvoices([VarPage]+1)
otherwise null,
VarPage = [VarPage]+1
],
each [Result]),
// Convert into a table
ConvertToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
// Expand data to get my ID list
ExpandColumns = Table.ExpandTableColumn(ConvertToTable, "Column1", {"id"}, {"id"}),
DataType = Table.TransformColumnTypes(ExpandColumns, {{"id", type text}}),
// Count rows to know when the table ends and we'll add a new delay to be safe for future queries in the dataflow
RowMax = Table.RowCount(DataType),
// Add an index to be able to keep track of my 'every multiple of 195 wait a minute and 5 seconds'
AddIndex = Table.AddIndexColumn(DataType, "Index", 1, 1, Int64.Type),
// Call function GetTLInvoicesDetailsID, queries on each ID
CallFunction_InvoiceDetails =
Table.AddColumn(AddIndex, "CallFunction_InvoiceDetails",
each
if [Index] = 1 or [Index] = RowMax or Number.Mod([Index], CallWait) = 0
then Function.InvokeAfter(() => GetTLInvoicesDetailsID([id]), WaitTime)
else GetTLInvoicesDetailsID([id])
),
DevelopData = Table.ExpandTableColumn(CallFunction_InvoiceDetails, "CallFunction_InvoiceDetails", {"Numéro de Chantier"}, {"Numéro de Chantier"}),
DataType_Details = Table.TransformColumnTypes(DevelopData, {{"XXX", type text}}),
in
DataType_Details
Perhaps not the most elegant solution, but it works 🙂
Thank you for your help!
The modulo should be sufficient.
GetTeamleaderInvoicesDetails:
(n)=> if n = 1 then "waited" else "direct"
TeamLeaderInvoicesList:
let
Source = {5 .. 10},
#"Converted to Table" = Table.FromList(
Source,
Splitter.SplitByNothing(),
null,
null,
ExtraValues.Error
),
#"Invoked Custom Function" = Table.AddColumn(
#"Converted to Table",
"Custom",
(k) =>
List.Generate(
() => 0,
each _ <= k[Column1],
each _ + 1,
each
if Number.Mod(_, 3) = 0 then
Function.InvokeAfter(() => GetTeamleaderInvoicesDetails(1), #duration(0, 0, 0, 5))
else
GetTeamleaderInvoicesDetails(2)
)
)
in
#"Invoked Custom Function"
You will notice that the later rows take longer and longer to populate.
Unfortunately, I was not able to make it work. But I eventually found an alternative :
let
SecondsWaiting = 65,// 60 seconds wait time between two bunches of 200 queries needed, 65 to be safe
WaitTime = #duration(0,0,0,SecondsWaiting),
CallWait = 195, // max 200 API calls per minute, set to 195 to be safe
// Call GetTL function to generate the list of IDs
Source = List.Generate(() =>
// Start loop
[Result =
try GetTLInvoices(1)
otherwise null,
VarPage = 1
],
// Call API until no result get turned up
each [Result]<>null,
each [Result=
try GetTLInvoices([VarPage]+1)
otherwise null,
VarPage = [VarPage]+1
],
each [Result]),
// Convert into a table
ConvertToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
// Expand data to get my ID list
ExpandColumns = Table.ExpandTableColumn(ConvertToTable, "Column1", {"id"}, {"id"}),
DataType = Table.TransformColumnTypes(ExpandColumns, {{"id", type text}}),
// Count rows to know when the table ends and we'll add a new delay to be safe for future queries in the dataflow
RowMax = Table.RowCount(DataType),
// Add an index to be able to keep track of my 'every multiple of 195 wait a minute and 5 seconds'
AddIndex = Table.AddIndexColumn(DataType, "Index", 1, 1, Int64.Type),
// Call function GetTLInvoicesDetailsID, queries on each ID
CallFunction_InvoiceDetails =
Table.AddColumn(AddIndex, "CallFunction_InvoiceDetails",
each
if [Index] = 1 or [Index] = RowMax or Number.Mod([Index], CallWait) = 0
then Function.InvokeAfter(() => GetTLInvoicesDetailsID([id]), WaitTime)
else GetTLInvoicesDetailsID([id])
),
DevelopData = Table.ExpandTableColumn(CallFunction_InvoiceDetails, "CallFunction_InvoiceDetails", {"Numéro de Chantier"}, {"Numéro de Chantier"}),
DataType_Details = Table.TransformColumnTypes(DevelopData, {{"XXX", type text}}),
in
DataType_Details
Perhaps not the most elegant solution, but it works 🙂
Thank you for your help!
Check out the July 2025 Power BI update to learn about new features.