Now Reading: PowerAutomate: Perform “Group By” operation on Data
-
01
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:
Purpose: Hold the unique data of 5 columns based on which we need to group data.
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.
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
Input: @outputs(‘List_Claims_-_NDIS_Claim’)?[‘body/value’]
Purpose: Create the map selecting the columns on which we need to group data.
Value Expression: union(body(‘Select_Unique_Columns’), body(‘Select_Unique_Columns’))
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”
]
}
}
}
Purpose: Set the values to 0 for them to be calculated.
Value: output from Parse JSON action
Value Expression: convertTimeZone(body(‘Parse_JSON’)?[‘SupportDeliveredFrom’], ‘AUS Eastern Standard Time’, ‘AUS Eastern Standard Time’, ‘yyyy-MM-dd’)
Value Expression: if(empty(string(body(‘Parse_JSON’)?[‘ClaimType’])),’null’,body(‘Parse_JSON’)?[‘ClaimType’])
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.
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’)}
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.
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.
From: @{variables(‘NDIS Final’)}
Columns: Automatic (To create the column headers based on data passed)
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 !!
Original Post https://ajitpatra.com/2020/09/28/powerautomate-perform-group-by-operation-on-data/
