“I have a Power Automate flow that’s adding rows from Excel into SharePoint on regular basis, but it’s so slow, is there a quicker way to find the missing rows?”
While Excel file is still a common way to store data, it’s often not the best tool to work with. Once you start building a flow based on the rows, it might get slower and slower until it’s unbearable. That’s why it might be a good idea to move some of the data into SharePoint and do the automation there. But it’s not easy to synchronise Excel with SharePoint list. How do you find the new rows? The rows that were added since the last synchronisation job? Do you compare all the rows with the SharePoint items, one by one?
Don’t use an ‘Apply to each’
While you might be tempted to take all the rows and check if there’s already a SharePoint item, don’t do that. You don’t want a loop over all the rows, it could run for ages.
Use ‘Select’ and ‘Filter array’ instead
It’s much better (and faster) to use the ‘Filter array’ action as when comparing two arrays. The Excel rows and SharePoint items are just two arrays after all.
List all rows in the Excel file and all items in the SharePoint list first.
‘Select’ only the unique values that connect the SharePoint items with the rows.
Add ‘Filter array’ that’ll take all the Excel rows, and filter only the those where the ‘Select’ output (SharePoint unique IDs) does not contain the unique values from the Excel file. Here you might need an expression to get to the column.
And that’s it, the output of ‘Filter array’ will be only the rows that don’t exist in the SharePoint list yet. Loop through these remaining rows and create them in the SharePoint list.
Summary
Comparing two arrays is one of the most time consuming operation if done wrong (using a loop). But if you use my favourite Power Automate actions ‘Select’ and ‘Filter array’ it doesn’t have to be so, and finding Excel rows missing in a SharePoint list is a matter of seconds!
Hi Tom,
The last “create item” inside the apply to each, how do you set it to get the items in the array? Thank you very much
Hello Toni,
it gets items from the array through the ‘Apply to each’. It loops through the filtered array of rows from the Excel files and processes them one by one and creates the item. It should be all doable using the available dynamic contents, just add the ‘Create item’ action, use the columns coming from the ‘Filter array’, and it’ll add the loop.