Delay API call when nested conditions are fulfilled, otherwise continue as normal

[Lezanne]

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 : 

let

  // Set variables

  SecondsWaiting = 65, // set the waiting time to 1'05 for safety

  WaitTime = #duration(0,0,0,SecondsWaiting), // prepared for the InvokeAfter

  RowMax = Table.RowCount(#"TeamleaderInvoices_List"),  // In this table, I have all the Invoices IDs I need to query (cannot do batch querying on that endpoint unfortunately and I need the information stored there), end of the loop contition

  RowMin = 0, // beginning of the loop condition

  VarPage = 0, // will be the iteration counter for the loop AND the variable used to query the custom function

  CallWait = 195, // set the waiting time condition to 195 for safety

  //

  //

  // Generate list

  Source = List.Generate(

// Start loop, initialize querying with the first item in the ID list table so 0

      () => [VarPage = RowMin],

// Generate list with conditions that : end of source table not met + at the beginning, end, and every 195 queries wait 1'05

      each

        [Result =

          if [VarPage]<= RowMax

          then

            if [VarPage] = RowMin

              or [VarPage] = RowMax

              or Number.Mod([VarPage], CallWait) = 0

            then

              // need a waiting time, to reset the querying allowance

              Function.InvokeAfter(() => GetTeamleaderInvoicesDetails([VarPage]), WaitTime)

            else

             // no need to wait, hand over the data API!

              GetTeamleaderInvoicesDetails([VarPage])

          else

            null // when exceed RowMax, set as null so as to exit the loop because we're finally done!

        ],

// Transformation : increment iteration counter by +1 to loop

      each [VarPage = [VarPage] + 1]

// Optional final transformation : nothing needed here I think?

    )

in

  Source

Technically the code continues later but that part works, my problem is here. Do you have any idea what makes PQuery think I want logical data when I only want the compiled list of records I get with my custom function GetTeamleaderInvoicesDetails? And how do I correct it? The problem likely is in my indented IFs but for the life of me I can't find the answer to my troubles on the forum.

Thank you in advance for your help and your time,
L.

[lbendlin]

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.

[Lezanne · Accepted 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!