topic Rest API Pagination Query (List. Generate) in Developer

Rest API Pagination Query (List. Generate)

Anonymous — Sat, 05 Mar 2022 19:16:37 GMT

HI . sorrry for posting this second or third time but could'nt find any solutions from the community. let me know if some who can help me out. Any power query expert here with Rest API's who can help me with List. Generate function query for pagination?
Issue: Pagination of API URL that allows 50 rows of data per Agent.
URL: https://vcc-na4.8x8.com/api/stats/agents/{Agent-ID}/Activities?n=1
Background: Created a function below which allows me to extract all Agent ID's(But only 50 rows per ID).
(id as text, n as number) as table=>

let

Source = Xml.Tables(Web.Contents("https://vcc-na8.8x8.com",

[RelativePath="/api/stats/agents/" &(id)&"/activities?n="&number.totext(n) ) ),

in
Source

Than a created a query below to extract all the rows as well watching curbal's video,
Video Link: https://www.youtube.com/watch?v=05yhwnuCjRw
Query:
let
Source = List.Generate(()=>
[result = try all_rows (1) otherwise try null, n=1],
each [result]<> null,
each [result = try all_rows ([n]+50) otherwise null, n=[n]+50],
each [result]

)
in
Source
but it shows this error,

Let me know if someone can solve my issue.
I am available for skype or zoom call anytime.if someone wants to understand the issue or resolve m,y issue that would be a huge help.

Re: Rest API Pagination Query (List. Generate)

V-lianl-msft — Wed, 09 Mar 2022 02:30:37 GMT

Hi @Anonymous ,

There seems to be no problem with PQ expression, but can't you get the content of the table?

I checked your website, which requires login.

Have you try connecting to REST APIs With OAuth2 Authentication In Power Query?

https://blog.crossjoin.co.uk/2021/08/29/connecting-to-rest-apis-with-oauth2-authentication-in-power-query-power-bi/

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

Re: Rest API Pagination Query (List. Generate)

Anonymous — Wed, 09 Mar 2022 07:37:56 GMT

Yes. I am unable to retrieve data by this query. but i have created two other queries which allows me to extract data but like all the rows. and one query which allows to extract all the id's.

PFB the video link to see what i want,
https://drive.google.com/file/d/1swxDQTqDQ9ASIj76CMNMhgWWCiDDw47d/view?usp=sharing

Best,
Saad.