Rest API Pagination Query (List. Generate)

[Saadii_360o]

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.

[mahoneypat]

Here is an example function with the syntax I believe you need. Since I can't access your API, I used the Star Wars API that supports paginated responses. To simulate your scenario with searching for different AgentIDs, I just changed the topic I was querying on (people, planets, vehicles, films). A different number of pages exist for each of those. You could update the function with your url and use AgentID in place of search.

(search as text)=>
let
    Source = List.Generate(
        ()=> [Result = try Json.Document(Web.Contents("https://swapi.py4e.com/api/" & search & "?page=1")) otherwise null, Page = 1],
        each [Result] <> null,
        each [Result =try Json.Document(Web.Contents("https://swapi.py4e.com/api/" & search & "?page="& Number.ToText([Page]+1))) otherwise null, Page = [Page] + 1],
        each [Result]
    )
in
    Source

Pat

[Saadii_360o]

If you are available on skype or zoom for 10 just minutes that would be great.

[Saadii_360o]

Hi, Thanks for the reply.
I have tried your way but it shows the error. KIndly let me know how to tackle it.

(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


Error

Thanks in advance.

[mahoneypat]

Couple things.

1. M is case sensitive, so you need to use Number.ToText

2. I am surprised that the id is wrapped in ( ). Is that needed? If so, I would think it would need to be inside the " ". Either remove the ( ), or update the code like this

[RelativePath="/api/stats/agents/(" & id & ")/activities?n=1"]

Pat

[Saadii_360o]

Hi I have followed the exact steps you mentioned earlier. the function works fine as i want it to be but i am unable to convert it into table.
PFB the Query,

(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

PFB the video that I have attached for you to see. you will be easily able to understand the situation.

https://drive.google.com/file/d/1-VhNR8C0CWIz5eKvXtg07MecwFmtdGJm/view?usp=sharing
Really looking forward for your reply.Thanks

[mahoneypat]

The issue is that it is using the first table to decide which columns to expand. One way to fix it would be to update the code in the formula bar with the list of field names (from the example you shows that had data).

Alternatively, you could use the approach shown in this video to keep all the fields from all the tables to be combined.

(2) The BEST way to combine files in Power BI so you don’t lose columns - YouTube

Pat

[Saadii_360o]

Hi, I think the query is giving me infinite tables in repsonse and i am unable to expand the tables in proper way.
PFB the way for you to undertsnd where I am stuck. it will save your time as well.

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

Really Looking forward for your help
best,
Saad.