Forum Discussion

jbruewer's avatar
jbruewer
Helper I
6 years ago
Solved

REST api request and loop by offset until no further records found

Hi,   i am facing the challange of requesting data from a REST service that limits the records by 250 without sharing how many records should retrieved at all. So i am not able to calculate in adva...
Need Help
  • artemus's avatar
    artemus
    6 years ago

    First, check the request metadata to see if it tells you how many records there are. 

    E.g. 

    let

       webData = Web.Contents("https://..."),

       webMetadata = Value.Metadata(webData)

    in

       webMetadata

     

    For doing a while loop in power you can instead use recursive functions like:

    let
   my_func = (startIndex) =>
      let
          webRequest = Web.Contents(...),
         ....,
         results = ...,
         numRecords = ....,
         if numRecords = page_size then
             results & my_func(startIndex + page_size)
         else
              results
in
   my_func(0)

     Note the @ used for recursion

mahoneypat's avatar
mahoneypat
Microsoft Employee
6 years ago

With some REST APIs, you can get a records count with $count.  I usually do that in one step and divide it by the number of records returned per call (250 in your case), and then use List.Numbers(0, countstep/250, 250).  That make a list that increments by 250 as many times as needed to get all the records.  You then convert that to table and the number column to text, and then concatenate that with the web call in a custom column.  From there, you can expand that column to combine the data from all the calls.

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

  • jbruewer's avatar
    jbruewer
    Helper I
    6 years ago

    Thanks for your contribution Mahoneypat,

     

    but my rest service doesn't provide any information about the total amount of records or how many pages have to be visited. 

    I honestly have the challenge to iterate unitl the amount of records per page are zro.

     

    //joerg

    • jbruewer's avatar
      jbruewer
      Helper I
      6 years ago

      Now, i am close to a solution by compiling this invoke function, except on thing ... please HELP

       

      let
          Source = (runID as number,offset as number, counter as number) => let

              counter = counter +1,
              webData = Json.Document(Web.Contents("https://serverurl.com",
                      [RelativePath="/index.php?/restapiFunc/" & Number.ToText(runID) & "&offset=" & Number.ToText(offset),
                      Headers=[#"Content-Type"="application/json"]])),

              resultList = if List.Count(webData) = 250 then
                                                webData & @all_results_by_id(runID, offset + counter*250, counter)
                                 else
                                               webData
              in
                  resultList

      in
           Source

      ----------------------------------------

       

      If the rest responses 2*250 records and the last one for instance 46 records, the overall result of this function keeps 500 and not 546. Means: The last set of less than 250 is missing....

       

      please help before it becomes to frustrating!

      • artemus's avatar
        artemus
        Microsoft Employee
        6 years ago

        The requests you are making are:

        0 - 250

        250 - 500

        750 - 1000

        1500 - 1750

        ...

         

        Instead of incrementing by 250 each time, you are incrementing by 250, 500, 750, ect...

         

        As I mentioned, you don't need counter, only offset.