In this post, we’ll see how we can perform aggregate function on data such as Sum or Count using PowerAutomate. Unlike LINQ in C# or SQL, it’s not very straight forward to do using PowerAutomate for which I thought of sharing the approach we had taken.
Recently, we got a requirement to calculate the sum of Quantity, after grouping the data on 5 columns NDISNumber, SupportDeliveredFrom, SupportNumber, ClaimType and CancellationReason in a custom entity Claim.
Below are the steps we followed to achieve the result:
Initialize 5 variables to hold unique values of each group-by column inside Apply to each action we are going to use further. Please see Note at the end of the post.
Initialize a variable to hold sum of Quantity and a dummy variable for calculation of Quantity as self reference is not supported in PowerAutomate as explained here.
After this step, we are ready for retrieving records with additional filter criteria of unique valued columns.
After this Apply to each action, we’ll get sum of Quantity based on grouped data.
Below is how the entire parent Apply to each action looks like.
After this Apply to each action, we’ll have grouped by data with sum of quantity in NDIS Final array variable.
Furthermore, we had requirement to store the unique data in CSV file in SharePoint. Below are the actions we’ve used to achieve that part.
After performing all the above steps, when we tested our PowerAutomate on the input data, we got our desired output in CSV file.
NOTE: In our requirement, we had to group the data based on 5 columns for which we had to use 5 different variables initially. If the number of columns on which we need to group the data is less or more then accordingly the number of variables will be less or more.