Power BI Pagination & Looping using List.Generate

[Saadii_360o]

Hi everyone. I am posting again because could'nt find my solution.
ISSUE:

URL: https://vcc-na8.8x8.com/api/stats/agents//agents/{agent-id}/activities?n={offset}

As you can see in above URL that I have to add agent id every single time and this API provides 50 rows per request.
NOTE: API does not provide any information on count etc
With help of @mahoneypat  I was able to create a function which allows me to extract all id's along with the pagination, PFB,

(id as text)=>

let

Source =

List.Generate( ()=>[result = try Xml.Tables(Web.Contents("https://vcc-na8.8x8.com",[RelativePath="/api/stats/agents/"&(id)&"/activities?n=1"])) otherwise null, n=1],

each [result]<> null,

each [result = try Xml.Tables(Web.Contents("https://vcc-na8.8x8.com",[RelativePath="/api/stats/agents/"&(id)&"/activities?n="&Number.ToText([n]+50)])) otherwise null, n=[n]+50],

each [result])

in

Source

Issue with the function:
1.Function is providing infinite tables. it is on constant loop I think.

2. When i try to invoke this function with Agent id's from new table it only formats the table with the first ID which is empty and provides null values.

3. It is on constant load. does'nt do anything.

PFB the link to my video which i have created for the better understanding. It will save your time to provide the solution.

Link: https://drive.google.com/file/d/1xbASl1EaziV3qp1ptZlzCtA-fbXnbJ3R/view?usp=sharing

API Documentation: https://support.8x8.com/cloud-contact-center/virtual-contact-center/developers/8x8-contact-center-st...

Requirment: Looking for a function which provides all the id's with all the rows(Pagination).


Really looking forward for someone's help.

Best,
Saad.

[v-jingzhang]

Hi @Saadii_360o 

The problem is that it's difficult to know how many activities every agent id does have, so it's not sure when it should end the loop. Maybe you can consider giving a large enough number as the loop time (for example 10 times) that every agent id will run this loop 10 times. After that, add a step to remove rows having empty tables. But if some agents have more activities than 500, this will lose records whose offset is larger than 500. 

Best Regards,
Community Support Team _ Jing