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

Thanks for your contribution, artemus!

 

I've still not reached my target but struggle brave forward 😉

 

My current development based on your input is:

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

let
counter = 0,



my_func = (offset as number) =>
    let
       counter = counter +1,
       webData = Json.Document(Web.Contents("https://servername.com",
             [RelativePath="/index.php?/api/url/2476&offset=" & Number.ToText(offset),
             Headers=[#"Content-Type"="application/json"]])),

       offset = List.Count(webData),

       resultList = if offset = 250 then
                                 resultList & my_func(offset + counter*250)
                          else
                                 resultList
       in
            resultList

in
     my_func(0)

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

By running thiw query i got following error:

An error occurred in the ‘’ query. Expression.Error: The name 'resultList' wasn't recognized. Make sure it's spelled correctly

 

by declaration of this variable like

resultList = {} // at the begin of the query

 

i go follwoing error:

An error occurred in the ‘’ query. Expression.Error: A cyclic reference was encountered during evaluation

 

Would be great to get some good example how the recursive loop works or even better... help to fix my code.

 

Thanks in advanced!

//joerg

artemus's avatar
artemus
Microsoft Employee
6 years ago

You cannot modify variables in power query.

 

The line:

counter = counter + 1

means declare a new variable counter with value equal to the origional counter + 1. In other words, this will always be equal to 1. You got the cyclic call error due to simply calling the same function over and over again.

 

The @ symbol means that you can refer to a variable that is part of the code which defines the variable. While this is usually used to recursivly call functions you could also put items inside itself, due to records being lazly evaulated. E.g.

let A = {@A} in A

which is equivenent in Java/c# to:

Object[] A = new Object[1];

A[0] = A;

 

What you need to do is, put any variable you want to modify in the parameter list of the function. So,

1. Remove counter, you don't need it. The recursive calll should be called with offset + 250

2. You will want resultList to be assigned to webData or webData union with the recursive call.