Combine Rest API Pagination and Multiple Loops

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

[lbendlin]

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

[Saadii_360o]

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

[v-rzhou-msft]

Hi @Saadii_360o ,

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.

[Saadii_360o]

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]

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.

[Saadii_360o]

Hi @lbendlin 
PFB the summarized issue in the below link,
It has a video link through which you will be easily able to explain the the issue and provide solution.

https://community.powerbi.com/t5/Developer/Power-BI-Pagination-amp-Looping-using-List-Generate/m-p/2...

Best,
Saad.