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

[jbruewer]

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 advance how often i've to call the api with the offset of 250.

I am looking for a solution to implement a List.Generate() until the last record has been read. I was wonder if i could loop until the record counts becomes 0.

I am pretty new in power bi, so don't judge me for my ugly code snippets 😉 But this is what i am currenctly expericen with:

my_list= List.Generate(
() => [offset = 250, offsetX = {0}],
each [offset] <> List.Count([offsetX]) ,
each [offsetX = List.Transform({},each Json.Document(Web.Contents("https://serverurl.com",
[RelativePath="/index.php?/api/api_url/" & "12" & " &limit=250&offset=" & ???,
Headers=[#"Content-Type"="application/json"]])))
]
),

maybe someone has already solved this challange and can share some snippets i sould use as well.

//joerg

[mahoneypat]

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]

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]

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!

[LP2803]

Hi Jbruewer,

I have a similar requirement to fetch data from Web : "https://webexapis.com/v1/meetings?meetingType=meeting&from=2019-01-22T04:00:00Z&to=2020-12-31T04:00:..."

and im trying to alter the above code you have provided. Would you mind explaining what is "@all_results_by_id" is?

I'm getting an error "The name 'all_results_by_id' wasn't recognized. Make sure it's spelled correctly."

[jbruewer]

Hi LP2803,

the name of the costom function i've created to invoke into the report is named: "all_results_by_id" and inside of the function it's call it self (recursive) by Filtered Result to date with MoM overview 

//joerg

[artemus]

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]

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

[artemus · Accepted solution]

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

[lolouk44]

Thanks @artemus for this, exactly what I was after.

Only one thing for the next reader, you forgot an "in" after your numRecords =

Query should be:

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

[jbruewer]

Thanks artemus for the fast response!

As i am a novice in power bi i like to learn also more.

in you snippet you used:

...

results & @my_func(startIndex + page_size)

it's the firsttime that i saw the "&" ... what is the explanation for this syntax?

Would be great to get your support!

//joerg

[artemus]

The & operator does the following:

1. Union 2 tables (as in the example above): #table(type table [A = number, B = text], {{1, "A"}, {2, "B"}}) & #table(type table [B = text, C = logical], {{"C", true}, {"D", false}}) = #table(type table [A = number, B = text, C = logical],  {{1, "A", null}, {2, "B", null}}, {{null, "C", true}, {null, "D", false}}) 
2. Combine 2 lists: {1, 2, 3} & {4, 5, 6} = {1, 2, 3, 4, 5, 6}
3. Combine 2 records (a record is a property bag or table row): [A = "Hi", B = 2] & [B = 5, C = #date(2020, 06, 27)] = [A = "Hi", B = 5, C = #date(2020, 06, 27)
4. Combine date with time: #date(2020, 06, 27) & #time(13, 45, 15) = #datetime(2020, 06, 27, 13, 45, 15)
5. Concatnate text: "Hello" & "Goodbye" = "HelloGoodbye"

[jbruewer]

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]

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.