cancel
Showing results for 
Search instead for 
Did you mean: 

Flow Group By

I was playing around with Flow and needed some way to group a collection of rows by a specific column. The articles online looked too complicated for something so simple, so I thought this would be a good place to get my hands dirty.

 

 

1. Add a "Request" trigger action which will allow the group by to be called by another Flow:

image.png

  

Add the following to the "Request JSON Body schema":

 
{
    "type": "object",
    "properties": {
        "key": {
            "type": "string"
        },
        "rows": {
            "type": "array",
            "items": {
                "type": "object"
            }
        }
    }
}

 

  • The "key" is the column in a table that you want to group by.
  • The "rows" are all the columns in the table that you want to be grouped by the key, including the key.

Note: The key data type is of type string therefore the rows whose column is the matching key also needs to be of type string.

 

2. Add a "Select" variable called "SelectGroupKeys". The From should contain the "rows" from the "Request" step and the map should be switched to text with the following expression:

 

item()?[triggerBody()?['key']]

 

 image.png

 

 

  • This step is going through all the possible values of the column key for each row and storing it as an array.

 

3. Add an "Initialize variable" with the the name "Initialize Distinct Keys". The variable name should be "varDistinctKeys" of type Array and the value should have an expression of: 

intersection(body('SelectGroupByKeys'),body('SelectGroupByKeys'))


image.png

 

  • This is simply taking all the key values and making it distinct like in SQL:image.png

     

4. Add another "Initialize variable" called "Initialize Results Array" with the Name "varResults" of type Array, leaving the "Value" empty.

 

5. Add the "Apply to each" (found under More). In the "Select an output from previous steps", add "varDistinctKeys" from step 3 above. Add a Filter array and in the "From", choose rows from the "HTTP" request. 

Set the left "Choose a value" to use an expression of:

item()[triggerBody()?['key']]

The right "Choose a value" is then set to "Current item"image.png

  • This step is filtering all the rows from the "HTTP" request by the "key".

 

6. Add an "Append to array variable" step under the "Filter array":image.png

  • To count the number of items for each key, I added this expression for the count:
length(body('Filter_array'))
  • The key contains the variable of the current item and the "Group" contains the body from the filter array (which already contains the grouping per key). 

The result of the "For each" should look like the following:image.png

 

7. Finally, add a response to send the result back to the calling Flow:

 

image.png

The "Response Body JSON Schema" contains the following:

{
    "type": "object",
    "properties": {
        "results": {
            "type": "array",
            "items": {
                "type": "object",
                "properties": {
                    "count": {
                        "type": "number"
                    },
                    "Name": {
                        "type": "string"
                    },
                    "Group": {
                        "type": "string"
                    }
                },
                "required": [
                    "count",
                    "Name",
                    "Group"
                ]
            }
        }
    }
}

 

To test the new flow

Create a new Flow using a flow button without any inputs. Then add a compose (Found under "Data Operations") with the following:

{
	"key" : "Status",
	"rows": [
			{"Name" : "Flow1", "Status": "Test Succeeded", "Run history" : "17 minutes ago" },
			{"Name" : "Flow2", "Status": "Test Succeeded", "Run history" : "59 minutes ago" },
			{"Name" : "Flow3", "Status": "Test Failed", "Run history" : "3 weeks ago" },
			{"Name" : "Flow4", "Status": "Test Failed", "Run history" : "4 weeks ago" }
		]
}
  • This example is generates the body of the request where I want all the rows grouped by the "Status".

Then add an HTTP step to call the Group By Flow using the "Method" of "POST" and the "Body" of the Compose "Output":

 image.png

Give the flow a name, save and run a Test. You should now check the run history, scroll to the HTTP step and review the output, which now contains the following result:

 

[
    {
        "count": 2,
        "key": "Test Succeeded",
        "Items": [
            {
                "Name": "Flow1",
                "Status": "Test Succeeded",
                "Run history": "17 minutes ago"
            },
            {
                "Name": "Flow2",
                "Status": "Test Succeeded",
                "Run history": "59 minutes ago"
            }
        ]
    },
    {
        "count": 2,
        "key": "Test Failed",
        "Items": [
            {
                "Name": "Flow3",
                "Status": "Test Failed",
                "Run history": "3 weeks ago"
            },
            {
                "Name": "Flow4",
                "Status": "Test Failed",
                "Run history": "4 weeks ago"
            }
        ]
    }
]
Meet Our Blog Authors
  • Working daily with Microsoft Cloud to deliver the needs of my company, my customers and various Microsoft communities and forums. | Office 365 | Flow | PowerShell | PowerApps | SharePoint |
  • Co-founder of https://plumsail.com, Office 365 and SharePoint expert. Passionate about design and development of easy to use, convenient and flexible products.
  • Microsoft Business Apps MVP. Owner of ThriveFast, an Office 365 consulting company.
  • 7x Microsoft Business Solutions MVP (CRM)
  • Solution Architect with Slalom, and organizer of the Boston Office 365 User Group, and long term SharePoint/Office 365 veteren. Find more at http://www.davidlozzi.com. Follow @DavidLozzi
  • I'm keen in MS technologies, SharePoint, Office 365 and development for them
  • Daniel is a Business Productivity Consultant & Microsoft Business Solutions MVP who is very enthusiastic about all things Office 365, Microsoft Flow, PowerApps, Azure & SharePoint (Online). Since the preview, Daniel has been working with Microsoft Flow and later on with Microsoft PowerApps. That led to him being awarded an MVP Award for Business Solutions. He loves to blog, present and evangelize about improving productivity in the modern workspace with these amazing tools!
  • Michelle is an Office 365 solution architect in Twin Cities, MN. She has been delivering business collaboration solutions for years with her focus on SharePoint and Office 365. Michelle is a recent board member of the Minnesota Office 365 User Group and has been a member of the SharePoint community since 2009. She is a frequent speaker at MNSPUG and SharePoint Saturday and co-chaired the Legal SharePoint User Group for 4 years. Her most frequent projects have involved rolling out a large deployment of Office 365, SharePoint Online intranet, build of a "CHAMPS" Office 365 user adoption program and most recently, SharePoint On-Premise to Online Migration. Michelle is very excited about cloud technology as it is shifting her IT Pro focus to collaboration strategy and technical adoption.
  • I'm a Microsoft Office Servers and Services MVP with a special interest in SharePoint, Office 365, Microsoft Flow, Microsoft Teams and PowerApps. I work at Triad Group Plc ( https://triad.co.uk)
>