Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Combine Rest API Pagination and Multiple Loops

HI Everyone. I hope you all are having a wonderful day. Background: I want extract all data from Rest API but the issue is that its URL provides data of 50 rows per Agent-ID. URL:  https://vcc-na8...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
lbendlin's avatar
lbendlin
Super User
4 years ago

Your function is missing the page number

 

(id as text,page as text) as table=>
let
Source = Xml.Tables(
Web.Contents(
"https://vcc-na8.8x8.com",
[RelativePath=
"/api/stats/agents/"
&(id)&
"/activities?n=" & page]
)
),

in
source
Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi. Yes already tried the above function but it does not let me invoke the function. Instead shows error

.and also it does.nott work on blow query for pagination,

 

= list.generate (()=>

[Output = try Factivity (1) otherwise null, n=1]

Each [output] <> null,

Each [output = try Factivity ([n]+50) otherwise null, n=[n]+50],

Each [output])

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    I think use function List.Generate in Power Query is a good way.

    How to use List.Generate to make API Calls in Power Query M

    Instead of pagination, you can also use List.Numbers along with Skip or Offset in the web calls to overcome the row limit.  This video walks through it.

    Power BI - Tales From The Front - REST APIs - YouTube

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      I have tried list. generate finction but it does not provide values. Also I have watched the tales from front video but it requires count of the API rows which i don't have.

      • lbendlin's avatar
        lbendlin
        Super User
        4 years ago

        You can use List.Generate to peek into the response and see if at a given offset no more data is returned.

        You could also use recursive functions but these generally do not work well when you have to return a lot of data.

         

        As you may imagine - without access to your API it is very difficult to help you with the code. That's a problem for any scenario that requires authentication/authorization.  Pity that they don't have dummy/test user accounts.