cancel
Showing results for 
Search instead for 
Did you mean: 
Ants

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"
            }
        ]
    }
]
About the Author
  • Experienced Consultant with a demonstrated history of working in the information technology and services industry. Skilled in Office 365, Azure, SharePoint Online, PowerShell, Nintex, K2, SharePoint Designer workflow automation, PowerApps, Microsoft Flow, PowerShell, Active Directory, Operating Systems, Networking, and JavaScript. Strong consulting professional with a Bachelor of Engineering (B.E.) focused in Information Technology from Mumbai University.
  • I am a Microsoft Business Applications MVP and a Senior Manager at EY. I am a technology enthusiast and problem solver. I work/speak/blog/Vlog on Microsoft technology, including Office 365, Power Apps, Power Automate, SharePoint, and Teams Etc. I am helping global clients on Power Platform adoption and empowering them with Power Platform possibilities, capabilities, and easiness. I am a leader of the Houston Power Platform User Group and Power Automate community superuser. I love traveling , exploring new places, and meeting people from different cultures.
  • Read more about me and my achievements at: https://ganeshsanapblogs.wordpress.com/about MCT | SharePoint, Microsoft 365 and Power Platform Consultant | Contributor on SharePoint StackExchange, MSFT Techcommunity
  • Encodian Owner / Founder - Ex Microsoft Consulting Services - Architect / Developer - 20 years in SharePoint - PowerPlatform Fan
  • Founder of SKILLFUL SARDINE, a company focused on productivity and the Power Platform. You can find me on LinkedIn: https://linkedin.com/in/manueltgomes and twitter http://twitter.com/manueltgomes. I also write at https://www.manueltgomes.com, so if you want some Power Automate, SharePoint or Power Apps content I'm your guy 🙂
  • I am the Owner/Principal Architect at Don't Pa..Panic Consulting. I've been working in the information technology industry for over 30 years, and have played key roles in several enterprise SharePoint architectural design review, Intranet deployment, application development, and migration projects. I've been a Microsoft Most Valuable Professional (MVP) 15 consecutive years and am also a Microsoft Certified SharePoint Masters (MCSM) since 2013.
  • Big fan of Power Platform technologies and implemented many solutions.
  • Passionate #Programmer #SharePoint #SPFx #M365 #Power Platform| Microsoft MVP | SharePoint StackOverflow, Github, PnP contributor
  • Web site – https://kamdaryash.wordpress.com Youtube channel - https://www.youtube.com/channel/UCM149rFkLNgerSvgDVeYTZQ/