“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’
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.
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!