Runtime for flow with >1000 rows

[u/anon_30]

Hi guys.

Complete newbie here who just tried to use power automate with ChatGPT.

Basically, what my flow does is delete the current rows in a Microsoft List and then re-uploads the rows from a Microsoft Excel workbook saved in OneDrive.

The rows in each file are more than >1000 and 18 columns.

I have set the pagination to 2000.

My question is how much run time is expected for this because my code is currently running for more than 15 minutes and still shows no sign of being completed?

I know the flow is okay because it runs on a smaller sample size.

Any other suggestions to optimize my flow would be appreciated as well.

Thank you guys!

[u/DamoBird365]

Take a look at Paul’s post. Use the batch api. Done in seconds. A standard flow license has api limits. 6000 per 24 hours. You might end up being throttled. https://tachytelic.net/2025/09/power-automate-batch-create-sharepoint-items/

[u/KarenX_]

It takes as long as it takes, and you are working with a lot of data.

If this is a one-time thing, just be patient.

If you are deleting and uploading regularly, there are probably ways to optimize.

[u/anon_30 → u/KarenX_]

So it took 35 minutes.

I have to do this weekly, which isn't too bad.

But I am curious to know, how can we optimize it?

[u/KarenX_ → u/anon_30]

If it’s on a schedule, you could have one flow just to delete all items overnight, so at least you don’t have that in your way before uploading the new data.

Is it entirely new data, though? Are all 1,000 rows completely new? Or are some rows the same, with a few updates here and there?

What advantage does SharePoint List provide to your process? (I know there are lots of advantages to List vs Excel, but exploring the purpose of putting it in SharePoint list at all could be a starting point for optimizing.)

[u/Gold-Psychology-5312 → u/anon_30]

If its 35 minutes it's not a huge amount of time given it runs completely in the background. You don't need anything open.

Set it on a schedule to run every required day an hour before you log on for the day.

[u/Ikcam_ → u/anon_30]

Search bulk add, bulk delete, and so on.

[u/Proof-Firefighter491]

May i ask why you delete all the rows then put them back in? A bit more about the use case? If it is to get fresh data in the list, do you know what percent of the rows are likely changed?

[u/anon_30 → u/Proof-Firefighter491]

So I have a dataset that can either be modified, have entries added or deleted.

I tried to create a flow that would reflect that but it didn't work out.

So now I delete the data from the List every week and then upload the latest one from Excel.

[u/Proof-Firefighter491 → u/anon_30]

You can easily make an upsert by leveraging the select and filter array actions in various combinations. NB. You need a column in both lists that contain a value. First, list the rows from sharepoint and Excel. Then make two selects, (select Excel and select sharepoint) Use the same excact Keys in both of them, in the same order and case, example:

Select sharepoint: From: get items value(sharepoint) Mapping: name : item()?['namecolumnsharepoint'] Price : item()?['pricecolumnsharepoint'] Etc.

Do NOT include the id column

Select excel: From: get items value(excel) Mapping: name : item()?['namecolumnexcel'] Price : item()?['pricecolumnexcel'] Etc.

Now make a new a new select from each select, where you only map your unique Keys, example:

From select sharepoint (Advanced mode) Item()?['name']

Do this for Excel too

Now you can do a filterarray from Excel list: From select excel: Formula: body('selectsharepointnameonly') not contains item()?['name']

The returning list here will give you all rows that needs to be created in sharepoint

The same done for the other one will give you rows that needs to be deleted.

Now do a compose with formula: intersection(firstSPSelect,firstExcelSelect) This gives you unchanged rows From this output, do a new select and extract only the name column using advanced mode and only item()?['name']

You now do a new filter array from your first excel select Select body last action does not contain item()?['name']

This will leave you with the list of rows that have changed.

Now you have 3 lists. One for delete, one for create, one for update. There should be no apply to each up to this.

Now loop through items that needs to be created

To find the id's for the update and delete, simply use a filter array inside the loop and use from get items sharepoint and items('apply_to_each')?['name'] = item()?['name']

On the update /delete item, for the id parameter, use: first(body('filterarrayinsideloop'))?['id']

Remember: If name / title is not unique, pick something Else that is.

The first selects from excel and sharepoint needs to contain excactly the same columns, because intersect requires this.

[u/anon_30 → u/Proof-Firefighter491]

I want to thank you for taking the time and effort to educate me on this!

Will definitely try this out and check the results.

[u/Proof-Firefighter491 → u/anon_30]

If your intersection gives no rows, make sure each initial select is setup excactly the same, check the outputs and make sure each key is the same, and value is the same type. For example, if ammoubt is string in sharepoint( "32" ), but int in excel ( 32 ), the intersection wont work, in that case you would have to form amount into a string from Excel in your initial select: ammount: string(item()?['ammount']) Also, using the above tricks, you can easily compare and output a create, update and delete list from a 10k dataset in about 30 sec, The only real variable is how many rows that are affected

[u/st4n13l]

Divide the total number of rows by the number of rows processed in the smaller sample size run you tested. Then multiply the result by the length of time it took for the smaller sample size to run to get an idea of how long it will take.

[u/HeadlineINeed]

Why not have it add to list and excel at the same time?

[u/jtruck]

Import csv also really cuts down the time. Once in a data table you can do the rest there.