topic Re: Combine Rest API Pagination and Multiple Loops in Developer

Combine Rest API Pagination and Multiple Loops

Anonymous — Thu, 03 Mar 2022 19:21:56 GMT

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.

Re: Combine Rest API Pagination and Multiple Loops

lbendlin — Sat, 05 Mar 2022 02:15:09 GMT

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

Re: Combine Rest API Pagination and Multiple Loops

Anonymous — Sat, 05 Mar 2022 02:54:15 GMT

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

Re: Combine Rest API Pagination and Multiple Loops

Anonymous — Wed, 09 Mar 2022 09:04:11 GMT

Hi @Anonymous ,

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.

Re: Combine Rest API Pagination and Multiple Loops

Anonymous — Wed, 09 Mar 2022 10:14:48 GMT

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.

Re: Combine Rest API Pagination and Multiple Loops

lbendlin — Wed, 09 Mar 2022 13:11:33 GMT

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.

Re: Combine Rest API Pagination and Multiple Loops

Anonymous — Wed, 09 Mar 2022 13:23:12 GMT

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/2383290

Best,
Saad.