Power Automate

This post covers how to configure a Power Automate Flow to get items from a SharePoint list at maximum speed. Super useful if you have an interactive process and you do not want to keep users waiting.

This post is part of a three part series of blog posts, before proceeding please read this post which describes the objectives, pros and cons of each method. It also provides links to the exported flows and sample data which I used to build the flows:

The many ways to retrieve more than 5,000 items from a SharePoint list using Power Automate

If you have not used a parallel branch before, this blog post is worth reading.

Before getting into the technicalities, have a look at the complete flow. If you have already built the more SharePoint API version detailed in this post, it will make sense.

General Premise

This flow retrieves the SharePoint items using the same method as the flow provided in this post. But the work of collecting data from SharePoint is split over four parallel branches, which execute at the same time. Instead of waiting for a single batch of 5,000 to be collected before proceeding to get the next batch.

Changes from the SharePoint API Flow

Several changes were made to the SharePoint API flow:

Variable Declarations

There are some extra values declared and defined at the start of the flow which control how the four branches choose which section of data to return:

Get the Lowest ID from the SharePoint list using the REST API

The lowest ID from the list is retrieved using the HTTP request to SharePoint action and uses the following code:

_api/Web/Lists/GetByTitle('5000 Item Test')/Items?$select=ID&$filter=ID gt 0&$top=1&$orderby=ID asc

Get the Highest ID from SharePoint list using the REST API

Same as the above flow step, but repeated to get the highest ID, uses the following code:

_api/Web/Lists/GetByTitle('5000 Item Test')/Items?$select=ID&$filter=ID gt 0&$top=1&$orderby=ID desc

Gets the Total Item count from the SharePoint List using the REST API

The total item count of the list is returned by using the ItemCount method of the SharePoint API:

_api/Web/Lists/GetByTitle('5000 Item Test')/ItemCount

Declare the control variables for each parallel branch

Because there are four parallel threads, all of the variables which operate within each branch are declared four times.

In the previous example, there was a single integer, array and boolean variable to control the do until loop that was retrieving SharePoint data. Now those variables need to be declared for each parallel branch:

The main control block for the Parallel Branches

In the control block for the parallel branches, a compose action is used to divide the total item count by four to determine the amount of data each branch should retrieve:

div(variables('itemCount'),4)

The control mechanism for the do until loops was modified to be more efficient, by changing the step which checks if there were no results from SharePoint

if(
	empty(body('HTTP_to_SharePoint_to_get_First_Quarter_of_Results').value),true,
	if (
		less(length(body('HTTP_to_SharePoint_to_get_First_Quarter_of_Results').value), 5000),true,false
	)
	
)

This removes an iteration of the loop, by checking if either of the following conditions are true:

• The result set from SharePoint was empty
• The number of results returned from SharePoint was less than 5000

SharePoint API Query for each branch

The SharePoint API query is different for each parallel execution of the data collection.

Each branch collects data according to the batch size defined by dividing the total item count by four and begins at the lowest ID in branch one. ?Next, branch two begins at the end of branch one and so on, so that all items are collected.

Branch 1

_api/Web/Lists/GetByTitle('5000 Item Test')/Items?$select=ID,EmpNo,Title,Last,Furloughed,Telephone,Email,Brand&$filter=ID gt @{variables('intID')} and ID le @{add(body('getLowestID')?['value'][0]['ID'], mul(outputs('DivideTotalByFour'), 1))}&$top=5000"

Branch 2

_api/Web/Lists/GetByTitle('5000 Item Test')/Items?$select=ID,EmpNo,Title,Last,Furloughed,Telephone,Email,Brand&$filter=ID gt @{add(body('getLowestID')?['value'][0]['ID'], mul(outputs('DivideTotalByFour'), 1))}  and ID gt @{variables('intID2')} and ID lt @{add(body('getLowestID')?['value'][0]['ID'], mul(outputs('DivideTotalByFour'), 2))}&$top=5000

Branch 3

_api/Web/Lists/GetByTitle('5000 Item Test')/Items?$select=ID,EmpNo,Title,Last,Furloughed,Telephone,Email,Brand&$filter=ID ge @{add(body('getLowestID')?['value'][0]['ID'], mul(outputs('DivideTotalByFour'), 2))}  and ID gt @{variables('intID3')} and ID lt @{add(body('getLowestID')?['value'][0]['ID'], mul(outputs('DivideTotalByFour'), 3))}&$top=5000

Branch 4

_api/Web/Lists/GetByTitle('5000 Item Test')/Items?$select=ID,EmpNo,Title,Last,Furloughed,Telephone,Email,Brand&$filter=ID ge @{add(body('getLowestID')?['value'][0]['ID'], mul(outputs('DivideTotalByFour'), 2))}  and ID gt @{variables('intID3')} and ID lt @{add(body('getLowestID')?['value'][0]['ID'], mul(outputs('DivideTotalByFour'), 3))}&$top=5000

Compile all results and send back to PowerApps

The response step simply uses a Union action to bring all the SharePoint data back in a single step:

union(variables('arrItems'),variables('arrItems2'),variables('arrItems3'),variables('arrItems4'))

Summary

Using Parallel branches to fetch SharePoint data can provide a huge speed benefit over a standard do until loop at the expense of creating a more complicated flow but it can be worth it to provide the best possible user experience.

It may be easier to just download the exported flow and modify to your requirements than build it from scratch.

You can download the flow and sample data from this post.