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. Th...
  • 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!