Use Parallel Branching and Batching to Accelerate Performance in Power Automate Flow

During a recent project, we faced a challenging requirement from a client: they needed to upload an Excel sheet containing multiple Opportunity product records and have these added to CRM using Power Automate Flow. Initially, the process of transferring a large volume of records from Excel to CRM was time-consuming and adversely affected system performance.

To mitigate this issue, we implemented a solution to divide the Excel data into several batches and process them concurrently. This optimization significantly reduced processing time and improved overall system efficiency.

The Excel file we are working with includes columns for Product Number, Product Name, and Quantity, outlined below:

Step-by-Step Guide: Creating an Optimized Power Automate Flow in CRM

1. First, we created a File Type field on the Opportunity entity which takes the Excel file having Opportunity Product records.
2. We developed a Power Automate flow which triggers modification of the File type field.
3. In the next step, we added a “Download a file or an image” step and selected the File Type field, which provides the necessary file content.
4. Then we created a compose action to convert the file content into the string using the expressions string(body(‘Download_a_file_or_an_image’))

       This will give output as string of excel data.

5. Next, we created a compose Action with a line break to do this, simply press ENTER.

6. Next, we created a compose action to split each row of Excel by line break. For that, we used the expression split(outputs(‘Compose_CSV_content’),outputs(‘Compose_-_Line_Break’))

This will give us the array of rows.

7. Then we created compose Action in which we took out all the columns’ headings using the expression split(first(outputs(‘Compose_-_Split_Rows’)),’,’)

Below is the output from the above step:

8. Now we created a Select action to create an array of objects containing each record of Excel data.

skip(outputs(‘Compose_-_Split_Rows’),1): This expression skips the first row, which contains column headings.
outputs(‘Compose_-_Column_Names’)?[0]: This retrieves the column heading at index 0; in our case, “Product Number” is at index 0.
outputs(‘Compose_-_Column_Names’)?[1]: This retrieves the second column heading; in our case, “Product Name“.
trim(outputs(‘Compose_-_Column_Names’)?[2]: This trims any extra spaces from the last column heading, which is “Quantity” in our case.

split(item(),’,’)?[0]: Retrieves the value of the first column.
split(item(),’,’)?[1]: Retrieves the value of the second column.
if(empty(split(item(),’,’)?[2]),null,trim(split(item(),’,’)?[2])): Checks if the value in the last column is blank; if so, it sets it to null; if not, it trims any extra spaces.

This step provides us with an array of objects, where each object represents a row value.

9. Then we created a compose action to make batches of the array using the expression div(float(length(body(‘Select’))),5)

In this case, 5 batches are created, but you can adjust the number according to your needs. The expression calculates the batch size as a floating number based on the length of the array.

10. Then we created a compose action to make the batch size an integer and round it down using the expression int(sub(outputs(‘Batch_Size_in_float’), mod(outputs(‘Batch_Size_in_float’),1)))

This expression converts the batch size from a floating-point number to an integer by rounding down.

11. Then we added a parallel branch and added a Compose action in each branch with one batch.

Every branch includes an equally divided array of objects using the expressions:

• • For 1st batch expression: take(body(‘Select’),outputs(‘Batch_Size_in_integer’))

This expression takes the 1st batch.

• • For 2nd batch expression: take(skip(body(‘Select’),mul(outputs(‘Batch_Size_in_integer’),1)),outputs(‘Batch_Size_in_integer’))

This expression skips the 1st batch and takes the 2nd batch.

• • For 3rd batch expression: take(skip(body(‘Select’),mul(outputs(‘Batch_Size_in_integer’),2)),outputs(‘Batch_Size_in_integer’))

This expression takes the 3rd batch.

So as per your requirement, you just need to increase the number highlighted in red in the expression to obtain subsequent batches.

The output of each branch will be as follows:

12. In the next step, we added an Apply to Each action in each branch which takes the output of the Compose action containing a single batch using expressions like outputs(‘1st_batch’) for the 1st batch.
13. In each Apply to Each action, we added a List Rows action which fetches the Product record and applies a filter on the Product Name and Product Number fields

For accessing column values:

• • For Product Name: items(‘iterating_1st_batch’)?[‘Product Name’]
  • For Product Number: items(‘iterating_1st_batch’)?[‘Product Number’]
  • For Quantity: items(‘iterating_1st_batch’)?[‘Quantity’]

These expressions allow you to retrieve specific column values from the iterated batch of data within each Apply to Each action.

14. Next, we created an Apply to Each action on the Product List and added an “Add a row” action to create an Opportunity Order Product record, setting all the required fields to create an Opportunity Order Product record.

This step involves iterating through the Product List and creating Opportunity Order Product records based on the filtered Product Name and Product Number, ensuring all necessary fields are properly configured for each record creation.

Conclusion:

Implementing this batching functionality can effectively reduce the execution time of Power Automate Flow, thereby significantly enhancing performance.