How to get a Group By Subtotal

Grouping by, getting the sum, count number of items. These are pretty typical requirements for interacting with SharePoint lists in Power Automate. Today I will show you how you can retrieve the Subtotal of an Attendees list that is grouped by a Course field.

Inspiration

This question from zmansuri:

Create a flow that will go through the above list and count how many times candidate was absent.

Power Users Community thread: GroupBy and Count.

Attendees

SharePoint Online has some really great features to group and count columns. It is pretty easy to apply a group by to your column in a SharePoint list in the interface.

I am going to use an an Attendees SharePoint list. This list has two columns, Course (single choice) and Attendee (person). This list is configured to group by Course and Count the number of rows. When you configure it like that you get these nice subtotals. However, how can you retrieve these in Power Automate?

RenderOptions #5707271

Paul has written a nice blog about retrieving the SUM via the RenderListDataAsStream. At first I wasn’t seeing the subtotals in the response. After some trial and error it turned out you can use body parameters. Adding the OverrideViewXml and RenderOptions 5707271 to the body really helped. Suddenly I was seeing all kind of group properties, including a Course.COUNT.Group with the correct value.

Flow setup

1. Add a Manually trigger a flow trigger action.

2. Add two Initialize variable actions. Below is a table with the name, type and value for each of the variables.

3. Add an Send an HTTP to SharePoint action. Use a POST request, the URI and the body the code snippet below.

4. Add a Select action. Use the row field in the from and select the Course and Group Count items.

5. Add a Compose action. Use an expression with an intersection function to get the subtotals per course.

In the select it will still retrieve every single row, we don’t want that 😉

Showing the results

When running this you can see the following result in the compose below.

Now suddenly you can use this for all other kinds of other scenarios. Like keeping track of the course limit for example.

Happy testing!