The Challenge
Many a times we come across a scenario where we need to the aggregate functions in Power Apps for a SharePoint list/library.
Aggregate functions – Average, Max, Min, StdevP, Sum, and VarP
Aggregate functions in Power Apps are not delegable to the data source – meaning Power Apps will load a max number of 2000 records in memory for such queries (assuming the limit for delegation in your App has been set to 2000)
To learn about delegation – check out my delegation video series.
The Solution
SharePoint lists/libraries contains a Column Totals feature for calculating aggregate data for Number type columns
Now if SharePoint can calculate the Aggregate functions at runtime, I went on the lookout for a potential rest API call to fetch the Totals column values for a List/Library.
I came across this blog post from Dec 2015 by Piyush K Singh –
Once the REST API endpoint was available, fetching the totals column info now was simple as Power Apps can call a flow which would in turn execute the rest API call to get information related to the Totals column.
To get the Totals column value, the rest API endpoint is as follows:
Method: POST
Uri: _api/Web/Lists/getbytitle('Students')/RenderListDataAsStream
Body:
Where ViewXml – Comprises of your CAML Query if required, Row Limit of 1 for improved performance and the aggregate call to the Totals column operation desired.
Please note in above example “Score” is the internal name of my Number column in SharePoint
Now since Power Apps cannot make rest API calls, we will call a flow to get the item count.
In your Power App, add a button control.
Select the button and from the ribbon go to Action > Power Automate
This will open a dialog which will list all the flows that your account has access to and has a Power Apps trigger.
Select the “Create a new flow” button
This will showcase the list of templates associated with the Power Apps trigger.
Select the “Power Apps button” template.
Give the flow a meaningful name – “Power Apps Get Totals Column Info”
You can pass params from Power Apps to flow to make the flow more dynamic. Example: Pass list/library name, even pass CAML Query logic if desired. See example here: https://rezadorrani.com/index.php/2020/08/07/power-apps-count-rows-in-a-sharepoint-list-library-avoid-delegation/
In this demo scenario, we will not pass any parameters.
In the flow – Add the “Send an HTTP request to SharePoint” action to call the rest API endpoint
Configure as shown below:
In above screenshot, ‘Students’ – Name of the SP list, ‘Score’ is the internal name of the Number column in SharePoint.
‘SUM’ – is the desired Totals column aggregation to be returned.
| Total Function | Function Type |
| Count | COUNT |
| Average | AVG |
| Maximum | MAX |
| Minimum | MIN |
| Sum | SUM |
| Std Deviation | STDEV |
| Variance | VAR |
You an only call 1 aggregate function with a single REST API Call.
If you need more function types included, use parallel branch in flow and make more REST API calls as shown in the video.
Next add the Respond to a “PowerApp or flow” action
Add a Output property of type Number and call it Sum
Set the value of the property to the following expression
float( body('Sum')?['Row']?[0]?['Score.SUM'])
where ‘Sum’ – is the name of the Send an http request to SharePoint action
Score.SUM – Relates to the SUM total column value.
Save the flow and ensure there are no errors.
Now go back to Power Apps and select the button that should call the flow.
For the selected button, go to Action > Power Automate and selected the newly created flow from the dialog.
This will add a function call to flow on the OnSelect property of the button
Above formula will run the flow when the button is selected, make the REST API Call to get the Totals column information from SharePoint and send that result back to Power Apps which will be stored in a variable named “varTotals”.
To get the item count values from flow (rest API call) use the following
varTotals.sum
where varTotals = the name of the variable Set when calling flow
and sum = the name of the output property created in flow
This technique will give you the any aggregate column information like Sum, Average, Count, Min, Max etc. from your list or library in Power Apps and you will not run into any delegation issues.
Hope this helps.
Regards,
Reza
