Pagination is frequently used by APIs to divide large sets of data into manageable “pages.” In this post, I’ll show you how to efficiently paginate and collect all the results into an array in Power Automate—without using Apply to Each. I’ll also include a quick performance comparison so you can see the impact for yourself. The difference in speed will surprise you!
Use Case: The Office 365 Users Connector
I want to use an example where you can try this against your own tenant: the Office 365 Users connector. One of it’s actions is the Send an HTTP Request action. By default, it returns up to 100 records unless you specific a top parameter which can be increased to 1000. Now, if your tenant will never contain more than the 1000 users, then cool, you don’t need to worry about pagination… However, this wouldn’t be a scalable approach in many scenarios. If your dataset results contains more than that, we need to page through and collect all of the results. Furthermore, many APIs use pagination to surface their data so this method it’ll help to know how to work with it sooner or later. 🙂
The action’s outputs will include the value array of users, but also include an @odata.nextLink if they are more results on the next page. Use a Do Until loop to repeat the request until @odata.nextLink is no longer returned. Once there is no more next link, break out of the loop.
The Apply to Each Conundrum
Now that’s all well and good, but how do we collect all the results? Well, an array variable for starters. If you’ve spent any time with Power Automate, you’ve probably been told to use an Apply to Each to loop through array values and append each record to an array variable. And yes, this works, however, on larger datasets especially, this is going to be slow…You can turn on parallelism of the apply to each, but this can still be slow and also it will not append the records in order (if that matters to you). Moreover, you are consuming more APIs which could impact performance.
Using Union Function
Instead of Apply to Each, use a compose action to print the current records in the array variable, and then using union, we will union both the array values and the newest values from the pagination results. The union function also ensures it doesn’t add any duplicates. Then, use set, not append to the array. That part is key! We can’t append an append to another array. We want to “reset” the array each time with the newest union results that’ll also contain the past results.
Make sure you output the existing array values into a compose action (called “Existing Array” above). Since Power Automate doesn’t support self-referencing, union the compose action along with the latest page of results.
Designing the Flow
Here’s a quick recap of what we need to do in our Power Automate Flow:
- Initialize the variables
- Construct the Graph API query to collects our users
- Run a Do Until Loop to paginate the Office 365 Users. Do this until no more @odata.next link is present.
- Collect the results and union against the current results in the array
- Output the results
Comparing the Performance: Apply to Each vs Union
To compare the performance, I also created an equivalent flow instead using the apply to each method. Processing my 93 users in Azure, here are the results:
| Method | Time (Seconds) |
|---|---|
| Apply to Each | 22 |
| Union | 5 |
That’s over 4 times faster! The performance difference will be even greater the more records you are collecting.