Forum Discussion

Lezanne's avatar
Lezanne
New Member
3 years ago
Solved

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. 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.
Need Help
  • Lezanne's avatar
    Lezanne
    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_Details

     

    Perhaps not the most elegant solution, but it works 🙂

     

    Thank you for your help! 

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago

    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's avatar
      Lezanne
      New Member
      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_Details

       

      Perhaps not the most elegant solution, but it works 🙂

       

      Thank you for your help!