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

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.

  • jbruewer's avatar
    jbruewer
    Helper I
    6 years ago

    Thanks for the feedback artemus!

     

    but it's working as expected. The interface has two additional parameter: limit and offset.

    The offset has to increase as the offset number is used to start by each request.

     

    1 request: offset = 0

    2 nd request offset =250

    3 rd request offset = 500

    ....

    This is woking as the max. limit is 250 by default.

     

    Anyhow, the function is now working as expected and i've compiled a custom function for reuse in other cases.

    //joerg