Looping to Multiple id's and pagination in API using Power Query

[Saadii_360]

Hi everyone I hope you all are fine. Any power query expert here in rest API's
Really need some help. I'm stuck for days. 

Background:
I am extracting data from an rest api which has get request url like this,
PFB,
https://vcc-na8.8x8.com/api/stats/agents/{agent-id}/activities?

so in order to extract data of all agent-id's at once i used this function below,

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

Than i used the invoke function against agent id's to extarct each id succesfuly into new table.

BUT

The issue is that each id provides 50 rows only and than it moves to next agent-id,
PFB,

Is there a way to create or manipulate a function in such a way that it provides a pagination as well.

Solution required:
Keeping in mind that API does not provide any info. when loaded into power bi.
I need some one to guide or manipulate a current in such a way that i can extract data of all id's and complete data (more than 50 rows) in one request.

Thanks in advance.

[PhilipTreacy]

Hi @Saadii_360 

Yes this can be done but to do so you need to examine the response sent by the API to see if there are more records to fetch. The response should contain a field that tells the query if there is more data for that particular ID.

Can you please provide a sample API response so I can check it to see how paging works for it.

Or do you have documentation for the API that explains the same ?

Any chance you can share your full query code so I can work with that by making API requests?

Regards

Phil

[Saadii_360]

Also. The pagination should be in a way that it refreshed in power bi service aswell.

[Saadii_360o]

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.

[PhilipTreacy]

Hi @Saadii_360o 

Isn't this a duplicate of this question 

Re: Looping to Multiple id's and pagination in API... - Microsoft Power BI Community

regards

Phil

[Saadii_360]

Hi Phil thanks for the reply. Huge fan of your youtube channel.

PFB the API documentation,
https://bit.ly/3vyczJk

PFB the API response both in browser and in Power query,
Browser,

in Power Query,

Proof that there is more than 50 rows per id's,
So I created this function and query to extract all rows from one id,

let
source=(n as number)=>
let
Source = Xml.Tables(
Web.Contents(

"https://vcc-na8.8x8.com/api/stats/agents/ag3ZcvfXhXSYW2xDU3VfOYkw/activities?n="&Number.ToText(n)

)
)
in
source

in
source

Proof that there is more than 50 rows, (I have manually ckecked the last row, API does not provide any information about the count)

Query to convert it into table,

let
Source =

List.Generate(

()=>
[output =

try activity(1) otherwise null, n=1],

each [output]<> null,

each [output = try activity([n]+50) otherwise null, n=[n]+50],

each [output]

)

in
Source

BUT                                                                   

When i use the below query to extract all id's instead of one, it gives me 50 rows per id because im unable to do pagination in below function and query using the above pagination method, 
PFB the complete query which extracts all id's,

(id as text)as table=>

let
Source = Xml.Tables(

Web.Contents(

"https://vcc-na8.8x8.com",

[RelativePath=

"/api/stats/agents/"

&(id)&

"/activities?&n="
]
)
)
Next steps that converts it into table
1. Create a custom table with all id's,

2. Invoke custom function against these id's with that function,

3. than it gives me each id but with 50 rows only and thats the issue I WANT AL THE ROWS WITH ALL THE ID'S

I hope the problem and requirmement is clear. let me know if you need anything else,
sorry for the long reply but really stuck in this issue and i need to resolve this.
best,
Saad.

[mahoneypat]

Since you have the query below working with pagination with a hard-coded Id value, you just need to convert it to a function that accepts the Id value as an input.  You would then start with your list of Id values and invoke it to get a List of 50-row tables on each row, which you could expand to new rows, and then expand the column of tables.

Sorry I didn't have time to propose the new function using the pieces of M code throughout.

let
Source =

List.Generate(

()=>
[output =

try activity(1) otherwise null, n=1],

each [output]<> null,

each [output = try activity([n]+50) otherwise null, n=[n]+50],

each [output]

)

in
Source

[v-yingjl]

Hi @Saadii_360 ,

In this case seems like it belongs to the 'page number' is known so you may refer this video to do the pagination for the API which introduces it in details:

How to do Pagination on a REST API in Power Query (Part 1) 

When the 'page number' is unknown, it has better to use List.Generate(), see:

How to do Pagination without knowing the number of pages (Part 2) in Power Query | List.Generate 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Saadii_360]

H @v-yingjl  if you look at me reply already applied the step. but it only works for single id not for multiple id's.