Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jbruewer
Helper I
Helper I

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 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

1 ACCEPTED SOLUTION
artemus
Microsoft Employee
Microsoft Employee

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

View solution in original post

13 REPLIES 13
mahoneypat
Microsoft Employee
Microsoft Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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

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
Responsive Resident
Responsive Resident

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."

 

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
Microsoft Employee
Microsoft Employee

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.

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
Microsoft Employee
Microsoft Employee

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

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)

 

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
Microsoft Employee
Microsoft Employee

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"

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
Microsoft Employee
Microsoft Employee

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors