Group by Count in Power Automate

An interesting question by PSrini20 led me to try this unique method to get a count of rows from a Datastore (SharePoint in this case) grouped by 2 columns.

Problem:

A SharePoint list has the following data

The idea is to summarize this data in the format below:

While we can loop through the rows and derive the count of each status by Department, I thought if I can get SharePoint to do the heavy lifting for me.

SharePoint view

As you know that SharePoint views can get totals and subtotals based on the Grouping and Totals settings that you set.

Adding the view displays the results along with the count per grouping.

So far, so good. Now the challenge is to get those numbers in Power Automate.

Introducing RenderListDataAsStream

The properties provided in the link above can be added as query string parameters to manipulate the returned data.

This article explains how you can get the list data for a specific view.

Power Automate

We can leverage the Send http request to SharePoint action to retrieve data from the View we created

Output of this action is as follows and you can already spot the Grouped column totals being returned.

I tied up the dataset using a Parse JSON action

and final output look like this.

While it provides the totals grouped by Status and department, the array contains repetitive values that can be eliminated using the Union function to obtain distinct values.

Now, I have a matrix for Department X Status with the Count in each.

[
  {
    "Department": "Department 1",
    "Status": "Closed",
    "Count": "2"
  },
  {
    "Department": "Department 2",
    "Status": "Closed",
    "Count": "1"
  },
  {
    "Department": "Department 3",
    "Status": "Closed",
    "Count": "1"
  },
  {
    "Department": "Department 1",
    "Status": "In Progress",
    "Count": "1"
  },
  {
    "Department": "Department 2",
    "Status": "In Progress",
    "Count": "1"
  },
  {
    "Department": "Department 3",
    "Status": "In Progress",
    "Count": "1"
  },
  {
    "Department": "Department 1",
    "Status": "New",
    "Count": "3"
  },
  {
    "Department": "Department 2",
    "Status": "New",
    "Count": "1"
  },
  {
    "Department": "Department 3",
    "Status": "New",
    "Count": "1"
  }
]

Now you can use a combination of Create HTML action and few variables with an Apply to each loop to render the result in the desired format.

Manish has provided the necessary actions needed in response to the query posted by PSrini