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