Forum Discussion
Looping to Multiple id's and pagination in API using Power Query
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.
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