Flow running too slow || Merging Excel Files and adding merged content to SharePoint List

[PowerSoumyo]

I am creating a solution for one of our clients where users will be uploading three excel files with a common Customer ID column (which we can use as key column in Power Automate "Get a Row" action) and on clicking a button in PowerApp, a cloud flow will run that will consolidate/merge the three files based on the common Customer ID column and insert the merged data in a SharePoint list.

What I Have Done?

I have used "List all records" for first excel file and then used "Apply to Each" on that. Within this loop, I am using "Get Record" from 2nd and 3rd files based on key column value and finally creating a SharePoint list item with these values.

The Problem:

The flow is working fine functionally but the problem is that this flow is taking more than 5 mins to run even with just about 100 records and since this is called from PowerApp button, the flow is throwing time out error.

Question/Suggestion Needed:

Is there a better way to implement the flow to reduce the execution time? Or is this a limitation in Power Automate, i.e. for this use case, is it a bad design to launch the flow from PowerApp or is this simply not feasible? Or any alternate suggestion to implement this step outside Power Platform to improve performance?

[DamoBird365 · Accepted solution]

Hi @PowerSoumyo 

I've created a solution that queries 3 tables in a single excel sheet as a proof of concept.

I then use a single apply to each and apply the item of the values from the first table against two filter arrays where the id's match and capture the key values for each match in a compose action based on the first (expression) element.  I've assumed that each table ID is unique/primary.  I only run this on 5 records mind you, but it took 1-2 seconds.  You could try turning on concurrency for the apply to each.

My compose output is :

The final compose action is on the compose within the apply to each.  Pieter shows this method on SharePains.

If you want to see this in action quickly, copy the code below and paste into "New step" -> MyClipboard.

{"id":"4c9819a7-b17f-40b1-9234-871f-79066193","brandColor":"#8C3900","connectionReferences":{"shared_excelonlinebusiness":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_excelonlinebusiness/connections/shared-excelonlinebu-47960226-b338-4d03-bbc1-dcfb-43cc2fda"}}},"connectorDisplayName":"Control","icon":"data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=","isTrigger":false,"operationName":"DamoBird365Join3ListsEfficiently","operationDefinition":{"type":"Scope","actions":{"List_rows_present_in_a_table":{"type":"OpenApiConnection","inputs":{"host":{"connectionName":"shared_excelonlinebusiness","operationId":"GetItems","apiId":"/providers/Microsoft.PowerApps/apis/shared_excelonlinebusiness"},"parameters":{"source":"me","drive":"b!BOIVsACSM02nAvrKA5sQqce2eGDhytpJrD6Ky1g1xl_-qoaIOl6yRo2252_HSDPt","file":"01SRF3RDUV3GZNFNBIVVBK5H7INFDAGMAZ","table":"{B9BBB08D-9DEE-4D7C-B948-8B2A831D4217}"},"authentication":{"type":"Raw","value":"@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"}},"runAfter":{},"metadata":{"01SRF3RDUV3GZNFNBIVVBK5H7INFDAGMAZ":"/3Tables.xlsx","tableId":"{B9BBB08D-9DEE-4D7C-B948-8B2A831D4217}"}},"List_rows_present_in_a_table_2":{"type":"OpenApiConnection","inputs":{"host":{"connectionName":"shared_excelonlinebusiness","operationId":"GetItems","apiId":"/providers/Microsoft.PowerApps/apis/shared_excelonlinebusiness"},"parameters":{"source":"me","drive":"b!BOIVsACSM02nAvrKA5sQqce2eGDhytpJrD6Ky1g1xl_-qoaIOl6yRo2252_HSDPt","file":"01SRF3RDUV3GZNFNBIVVBK5H7INFDAGMAZ","table":"{F3C0055D-E52A-44B7-9618-C0A825794EB1}"},"authentication":{"type":"Raw","value":"@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"}},"runAfter":{"List_rows_present_in_a_table":["Succeeded"]},"metadata":{"01SRF3RDUV3GZNFNBIVVBK5H7INFDAGMAZ":"/3Tables.xlsx","tableId":"{F3C0055D-E52A-44B7-9618-C0A825794EB1}"}},"List_rows_present_in_a_table_3":{"type":"OpenApiConnection","inputs":{"host":{"connectionName":"shared_excelonlinebusiness","operationId":"GetItems","apiId":"/providers/Microsoft.PowerApps/apis/shared_excelonlinebusiness"},"parameters":{"source":"me","drive":"b!BOIVsACSM02nAvrKA5sQqce2eGDhytpJrD6Ky1g1xl_-qoaIOl6yRo2252_HSDPt","file":"01SRF3RDUV3GZNFNBIVVBK5H7INFDAGMAZ","table":"{51229393-78F0-4156-A7A6-219DA494959E}"},"authentication":{"type":"Raw","value":"@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"}},"runAfter":{"List_rows_present_in_a_table_2":["Succeeded"]},"metadata":{"01SRF3RDUV3GZNFNBIVVBK5H7INFDAGMAZ":"/3Tables.xlsx","tableId":"{51229393-78F0-4156-A7A6-219DA494959E}"}},"Apply_to_each":{"type":"Foreach","foreach":"@outputs('List_rows_present_in_a_table')?['body/value']","actions":{"FilterArray1Left2":{"type":"Query","inputs":{"from":"@outputs('List_rows_present_in_a_table_2')?['body/value']","where":"@equals(items('Apply_to_each')?['ID'], item()?['ID'])"},"runAfter":{}},"FilterArray1Left3":{"type":"Query","inputs":{"from":"@outputs('List_rows_present_in_a_table_3')?['body/value']","where":"@equals(items('Apply_to_each')?['ID'], item()?['ID'])"},"runAfter":{"FilterArray1Left2":["Succeeded"]}},"NewArray":{"type":"Compose","inputs":{"id":"@item()?['Id']","Forename":"@item()?['Forename']","Surname":"@first(body('FilterArray1Left2'))?['Surname']","Sex":"@first(body('FilterArray1Left3'))?['Sex']","Height":"@first(body('FilterArray1Left3'))?['Height']","Weight":"@first(body('FilterArray1Left3'))?['Weight']"},"runAfter":{"FilterArray1Left3":["Succeeded"]}}},"runAfter":{"List_rows_present_in_a_table_3":["Succeeded"]}},"Compose":{"type":"Compose","inputs":"@outputs('NewArray')","runAfter":{"Apply_to_each":["Succeeded"]}}},"runAfter":{}}}

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here

[DamoBird365]

Hey @PowerSoumyo 

How did you get on with this?

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here

[PowerSoumyo]

Hi @DamoBird365

Thanks for your response; it is indeed a good solution. I gave it a try this morning and till the Compose new Array step with the JSON output, it is working fine and taking around 30 secs for 100 records which is a significant improvement in performance. I believe the next step would be to parse this JSON output and insert the values in SharePoint list, correct?

[DamoBird365]

Hi @PowerSoumyo 

I believe that parsing is a preference if you want to call the fields dynamically.  Otherwise you can build an expression.  Try turning on concurrency on the apply to each - it will currently run that loop in sequence, so I think you have more efficiency to see there.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here

[PowerSoumyo]

Hi @DamoBird365 

Turning on Concurrency has brought down the execution time to under 5 seconds (with the default setting of 20 degree of parallelism) which is great. However, I didn't understand the expression part, i.e. how to take the json output from compose new array step and insert to SharePoint list using expression. Can you please elaborate on that or point me to any online resource? Thanks again!

[DamoBird365]

Hi @PowerSoumyo 

Superb!  That has to be a great improvement in efficiency.

Take a look at https://powerusers.microsoft.com/t5/Building-Flows/How-to-access-JSON-Array-items/td-p/83265

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here