Forum Discussion
Lezanne
3 years agoNew Member
Delay API call when nested conditions are fulfilled, otherwise continue as normal
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. Th...
- 3 years ago
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_DetailsPerhaps not the most elegant solution, but it works 🙂
Thank you for your help!
lbendlin
Super User
3 years agoThe 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.
Lezanne
3 years agoNew Member
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!