Recently, we encountered a requirement in one of our projects to integrate a SQL database with Power
Automate in order to retrieve records and send automated email notifications to respective managers.
The objective was to calculate the total number of hours logged by resources reporting to each
manager and share a consolidated summary via email.
In the email, we needed to group the records by manager and show the total hours for each group.
However, there were more than 1,000 records, and looping through each item to calculate the total
was taking too much time.
Currently, our database contains more than 9,000 records. In our Power Automate flow, we used
multiple actions to transform the data, which caused the execution time to exceed 34 minutes. This
was taking too long and needed optimization.
To transform the data and calculate the totals, we used multiple For Each loop in the flow. However,
using these loops significantly increased the execution time. Therefore, we wanted to optimize the
solution by performing aggregation (such as summation) without using loops, in order to reduce the
overall execution time.
Now, let’s take a deeper look at the solution we used to calculate the total hours grouped by manager
without using a loop.
First, we used the Select action to extract and store the Manager IDs from the records. Then, we
added a Compose action and used an expression to get the unique Manager IDs.
After retrieving the unique Manager IDs, we used an Apply to each action to iterate through those
unique IDs. Inside the loop, we added a Filter action to filter the records associated with each specific
manager.
Once we had the filtered records for a manager, the next step was to calculate the total of the Hours
column for all resources under that manager.
Instead of using another loop, we followed a different approach.
Step 1: Store the filtered data in a JSON object
We used a Compose action to store the filtered records in a JSON structure like this:
{
“data”: {
“rows”: @{body(‘Filter_Resources’)}
}
}
Step 2: Convert the JSON object to XML
Next, we converted this JSON object into XML format using the xml() function:
xml(outputs(‘Convert_In_Object’))
Step 3: Use XPath to calculate the total
Now comes the most important part using XPath.
XPath allows us to query the XML data and extract values without using a loop. With XPath, we can
directly apply the sum() function to calculate the total of a specific column.
Here is the expression we used:
xpath(
outputs(‘Convert_In_XML’),
‘sum(//data//Hours)’
)
This expression calculates the total of the Hours column for all filtered records without using an
additional Apply to each loop.
Conclusion: By using the XPath function, we can apply multiple operations directly on
XML data. In this solution, we used XPath to calculate the total hours without using a loop.
This approach helped us avoid multiple Apply to each action, reduce execution time, and
improve the overall performance of the flow. javascript:”javascript:”javascript:”
writer.editor.GO_TO_TOP
writer.editor.GO_TO_BOTTOM
Thank you, Nisha Pawar for your valuable inputs to this blog
D365 Feeds 