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.8x8.com/api/stats/agents/{Agent-ID}/activities?n=1
So far I've been able to create a function in Power query which allows me to extract all the ID's,
PFB,

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

in
source
Than I invoked this function against the Agent id's column in new table and received al the id's with 50 rows.

Issue:
It provides 50 rows per ID. I need to do a pagination in above function so that it gives me all of the rows with these all Id's.

PFB the API documentation.
https://bit.ly/3hBkoWE

Let me know if someone has the way to solve my problem.

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

6 Replies

  • 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])