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

Sending
User Review
0 (0 votes)

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:

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

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. Use Parallel Branching and Batching to Accelerate Performance in Power Automate Flow
  2. We developed a Power Automate flow which triggers modification of the File type field. Use Parallel Branching and Batching to Accelerate Performance in Power Automate Flow
  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. Use Parallel Branching and Batching to Accelerate Performance in Power Automate Flow
  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’)) Use Parallel Branching and Batching to Accelerate Performance in Power Automate Flow

       This will give output as string of excel data. Use Parallel Branching and Batching to Accelerate Performance in Power Automate Flow

  1. Next, we created a compose Action with a line break to do this, simply press ENTER. Use Parallel Branching and Batching to Accelerate Performance in Power Automate Flow
  1. 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’)) Use Parallel Branching and Batching to Accelerate Performance in Power Automate Flow

This will give us the array of rows. Use Parallel Branching and Batching to Accelerate Performance in Power Automate Flow

  1. Then we created compose Action in which we took out all the columns’ headings using the expression split(first(outputs(‘Compose_-_Split_Rows’)),’,’) Use Parallel Branching and Batching to Accelerate Performance in Power Automate Flow

Below is the output from the above step: Use Parallel Branching and Batching to Accelerate Performance in Power Automate Flow

  1. 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.

Use Parallel Branching and Batching to Accelerate Performance in Power Automate Flow
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.

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

  1. 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. Use Parallel Branching and Batching to Accelerate Performance in Power Automate Flow

  1. 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))) Use Parallel Branching and Batching to Accelerate Performance in Power Automate Flow

This expression converts the batch size from a floating-point number to an integer by rounding down. Use Parallel Branching and Batching to Accelerate Performance in Power Automate Flow

  1. Then we added a parallel branch and added a Compose action in each branch with one batch. Use Parallel Branching and Batching to Accelerate Performance in Power Automate Flow

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. Use Parallel Branching and Batching to Accelerate Performance in Power Automate Flow

The output of each branch will be as follows:Use Parallel Branching and Batching to Accelerate Performance in Power Automate Flow

  1. 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.
  2. 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. Use Parallel Branching and Batching to Accelerate Performance in Power Automate Flow

  1. 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. Use Parallel Branching and Batching to Accelerate Performance in Power Automate Flow

Conclusion:

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

Map My Relationship

The post Use Parallel Branching and Batching to Accelerate Performance in Power Automate Flow first appeared on Microsoft Dynamics 365 CRM Tips and Tricks.