Boost Your Summing Game: Data Aggregation in Power Automate

In the quest for effective data management within Power Automate, summing data columns from a table, such as aggregating the Sales Amount (Actual) from the Item ledger table to ascertain total sales over a specific period and/or for a particular item, is a common task.

Traditionally, this task has been tackled using the "apply to each" method. This conventional method involves iterating through each record fetched from a "Find" action on the item ledger table, capturing the sales amount in each iteration, and incrementing a variable to keep track of the total sales.

While the conventional "apply to each" method serves its purpose, it falls short on the efficiency spectrum. It's not only resource-intensive but also poses a risk of exhausting your daily request limits, especially when dealing with a substantial amount of data. This calls for a more efficient, less cumbersome approach to summing Business Central data in Power Automate.

The spotlight now shifts to a more streamlined method that not only simplifies the task but does so with fewer steps, making it a far more efficient alternative. This refined approach significantly reduces the number of actions required, thereby conserving valuable resources and time. Let’s delve into this novel method that promises a marked improvement in how you sum Business Central data in Power Automate, ensuring you achieve more with less.

The Conventional Approach

Let's take a look at the conventional approach in greater detail. While you might use different combinations of the compose, variables, or arrays in your code, the overall approach is the same as the one that I layout before you.

To give a brief understanding of the conventional approach, here's a step-by-step breakdown:

1. Initialization: Start by initializing your sales accumulation variable.

2. Item Retrieval: Find the Item Ledger Items, specifically sales type transactions for item number 1928-S.

3. Looping with "Apply to Each": Here, iterate through each record fetched from the previous step. In each iteration, capture the sales amount and update your sales total variable.

1. Initialize your sales accumulation variable.

Initialize Sales Total Accumulator

2. Find the Item Ledger Items. In this example we will retrieve only sales type transactions for item number 1928-S. This step is identical with what we will do in the streamlined approach.

Business Central Item Ledger Entries Find Step

3. Insert an Apply to Each step. In the loop we will first save the running Sales Total accumulator. Next we will capture the Sales Amount from the current record. Finally we will add the Sales Total and Sales Amount together and save the result in the Sales Total variable.

The Apply-To-Each Loop

Here is the code from the Set Sales Total step:

add(outputs('Compose_Save_Sales_Amount'),outputs('Compose_Save_Sales_Total'))

4. Create a display-friendly version of the final Sales Total with the following code:

Compose a FormatNumber of the Sales Total

Here is the code for the Compose Display step:

formatNumber(variables('salesTotal'),'$###,##0.00')

The Streamlined Approach

Now, let’s delve into the streamlined approach which reduces the number of steps and increases efficiency:

1. Item Retrieval: Identical to the conventional approach, retrieve the desired Item Ledger Items.

2. Selection: Introduce a Select step, mapping the Sales Amount (Actual) from the previous step.

3. Composition: In a Compose step, employ the following JSON code, referencing the output from the Select step:

4. Total: In a Compose step, total the Sales Amount in the JSON using a xpath.

1. The first step is the identical Business Central Item Ledger Find step that we used in the conventional approach.

Business Central Find Item Ledger Entries

2. Insert a Select step. Insert into the From field the value produced by the Find in step #1. The Map field is the Sales Amount (Actual) produced from step #1.

Select Sales Amount Step

3. Insert a Compose step. Enter the following JSON code and reference output from the prior Select step.

Compose Step with JSON code

Here is the code to create the JSON:

{
  "root": {
    "sales": @{body('Select_Sales_Amount')}
  }
}

4. Create the display version of the output.

Compose Summing and Formatting the Sales Amount

Here is the code to total and format the sales amount:

formatnumber(xpath(xml(outputs('Compose_JSON')), 'sum(/root/sales)'),'$###,##0.00')

Comparing the Two Approaches

On analyzing both methods, it's clear that the new approach is significantly more streamlined. The conventional approach demands a total of 7 steps, including 3 pre-loop steps, 3 loop steps, and 1 post-loop step. Contrastingly, the new method accomplishes the task in just 4 succinct steps.

Chart-1. Performance Review of the Conventional Approach

Take an example where you process 30 item ledger entries; the conventional method would necessitate 94 steps (30*3 loop steps + 4 additional steps). However, the new method requires only 4 steps, demonstrating a stark improvement in efficiency.

Moreover, a performance review reveals that the conventional method shown in Chart-1 averages a run duration of 6.99 seconds, while the new approach show in Chart-2 slashes this time down to 3.57 seconds. Not only does this reduce the number of actions, but it also significantly trims down the average run duration, proving the new approach to be a more efficient and faster alternative for summing Business Central data in Power Automate.

In a landscape where every second counts, transitioning to this new approach could greatly optimize your data summing processes, ensuring more gets done in less time and within fewer steps.

Chart-2. Performance Review of the Streamlined Approach

Conclusion

The journey through the conventional and streamlined approaches for summing data in Microsoft Dynamics 365 Business Central via Power Automate illuminates a significant stride towards operational efficiency. As demonstrated, the traditional "apply to each" method, although functional, is notably more resource-intensive and time-consuming compared to the streamlined approach.

The streamlined approach unfolds a pathway to not only a drastic reduction in the steps required to achieve the desired outcome but also a commendable cut in the run duration. This method is a testament to how evolving methodologies in Power Automate can lead to quicker, more efficient data processing, ultimately contributing to better resource management and faster decision-making within your organization.

Furthermore, the reduction in the number of actions needed directly translates to a lesser burden on your daily request limits, which is a substantial advantage, especially in a data-intensive environment. The example provided, where the streamlined approach required only 4 steps as opposed to the 94 steps needed in the conventional approach for processing 30 item ledger entries, unequivocally underscores the efficiency of this new method.

The visual representation through Chart-1 and Chart-2 vividly encapsulates the performance enhancement, making a compelling case for transitioning to the streamlined approach.

As businesses continually seek ways to optimize operations and enhance productivity, adopting such streamlined methods in Power Automate can be a game-changer. It not only refines the process of summing data but also sets a precedent for exploring more efficient methodologies in handling various other tasks within Microsoft Dynamics 365 Business Central.

In a nutshell, the streamlined approach is not merely an alternative but a significant upgrade to the conventional method. It beckons a paradigm shift that could greatly optimize your data summing processes in Power Automate, reaffirming that with the right approach, achieving more with less is not a mere aspiration but a tangible reality.