PowerAutomate: Perform “Group By” operation on Data

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:

**Action**: Initialize Variable(Variable)
**Purpose**: Hold the unique data of 5 columns based on which we need to group data.

**Action**: Initialize Variable(Variable)
**Purpose**: Resultant array to hold unique data along with sum of quantity

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.

**Action**: List Records(CDS current environment)
**Purpose**: Get the records on which we are going to perform group-by operation.
**Filter Query**: _dxc_billingclaimid_value eq @{outputs(‘Get_Billing_record’)?[‘body/dxc_billingid’]} and statuscode eq 1 and dxc_ndistype eq 282210000
**Select Query**: dxc_claimid,dxc_registrationnumber,dxc_ndisnumber,dxc_supportsdeliveredfrom,dxc_supportsdeliveredto,dxc_supportnumber,dxc_claimreference,dxc_quantity,dxc_hours,dxc_unitprice,dxc_gstcode,dxc_claimtype,dxc_cancellationreason

**Action**: Select(Data Operation)
**Input**: @outputs(‘List_Claims_-_NDIS_Claim’)?[‘body/value’]
**Purpose**: Create the map selecting the columns on which we need to group data.

**Action**: Set Variable(Variable)
**Value Expression**: union(body(‘Select_Unique_Columns’), body(‘Select_Unique_Columns’))

**Action**: Apply to each
**Input**: variables(‘NDIS’)

**Action**: Parse JSON(Data Operation)
**Input**: @items(‘Apply_to_each_Unique_Data’)
Schema: {
    “type”: “object”,
    “properties”: {
        “SupportNumber”: {
            “type”: “string”
        },
        “SupportDeliveredFrom”: {
            “type”: “string”
        },
        “ClaimType”: {
            “type”: [
                “integer”,
                “null”
            ]
        },
        “NDISNumber”: {
            “type”: “string”
        },
        “CancellationReason”: {
            “type”: [
                “integer”,
                “null”
            ]
        }
    }
}

**Action**: Set Variable(Variable)
**Purpose**: Set the values to 0 for them to be calculated.

**Action**: Set Variable(Variable)
**Value**: output from Parse JSON action

**Action**: Set Variable(Variable)
**Value Expression**: convertTimeZone(body(‘Parse_JSON’)?[‘SupportDeliveredFrom’], ‘AUS Eastern Standard Time’, ‘AUS Eastern Standard Time’, ‘yyyy-MM-dd’)

**Action**: Set Variable(Variable)
**Value Expression**: if(empty(string(body(‘Parse_JSON’)?[‘ClaimType’])),’null’,body(‘Parse_JSON’)?[‘ClaimType’])

**Action**: Set Variable(Variable)
**Value Expression**: if(empty(string(body(‘Parse_JSON’)?[‘CancellationReason’])),’null’,body(‘Parse_JSON’)?[‘CancellationReason’])

After this step, we are ready for retrieving records with additional filter criteria of unique valued columns.

**Action**: List Records(CDS Current Environment)
**Purpose**: Get the records from the same entity again with the additional conditions of values of the columns set above to accumulate Quantity.
**Select Query**: dxc_claimid,dxc_registrationnumber,dxc_ndisnumber,dxc_supportsdeliveredfrom,dxc_supportsdeliveredto,dxc_supportnumber,dxc_claimreference,dxc_quantity,dxc_hours,dxc_unitprice,dxc_gstcode,dxc_claimtype,dxc_cancellationreason
**Filter Query**: _dxc_billingclaimid_value eq @{outputs(‘Get_Billing_record’)?[‘body/dxc_billingid’]} and statuscode eq 1 and dxc_ndistype eq 282210000 and dxc_ndisnumber eq ‘@{variables(‘Current NDIS ID’)}’ and dxc_claimtype eq @{variables(‘Claim Type Condition’)} and dxc_supportnumber eq ‘@{variables(‘Current Support Number’)}’ and dxc_supportsdeliveredfrom eq @{variables(‘Current Support Delivered From’)} and dxc_cancellationreason eq @{variables(‘Cancellation Reason Condition’)}

**Action**: Apply to each
**Input**: @{outputs(‘List_Claims_Specific_Records’)?[‘body/value’]}

**Action**: Set Variable(Variable)
**Value Expression**: add(variables(‘Quantity’), items(‘Apply_to_each_Claim_Record’)?[‘dxc_quantity’])

**Action**: Set Variable(Variable)
**Value**: @{variables(‘Dummy Quantity’)}

After this Apply to each action, we’ll get sum of Quantity based on grouped data.

**Action**: Append to array variable(Variable)
**Purpose**: Construct JSON object with the required attributes and quantity evaluated above.

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.

**Action**: Create CSV Table(Data Operation)
**From**: @{variables(‘NDIS Final’)}
**Columns**: Automatic (To create the column headers based on data passed)

**Action**: Create file(SharePoint)
**Site Address**: SharePoint Site Address
**Folder Path**: Path to folder where the file needs to be stored.
**File Name**: MIND – @{convertTimeZone(utcNow(), ‘GMT Standard Time’, ‘AUS Eastern Standard Time’, ‘ddMMyyyy_HHmmss’)}.csv
**File Content**: @{body(‘Create_CSV_NDIS_Claim’)}

After performing all the above steps, when we tested our PowerAutomate on the input data, we got our desired output in CSV file.

Input Data:

Output Data:

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.

Hope it helps !!