Let's POWER Automate

From no-code to low-code

Menu
  • Expressions
  • Filters
  • General
  • Application specific solutions
    • Dataverse
    • Excel
    • Forms
    • Outlook
    • Planner
    • Power Apps
    • SharePoint
    • Teams
  • Triggers
  • Ready to use solutions
    • Approval Process Solution
    • Task Delegation App
    • The Ultimate Power Automate expressions cheat sheet
    • Power Automate HTTP requests to SharePoint cheat sheet
    • Power Automate HTTP requests to Graph API cheat sheet
  • ABOUT ME
  • Get help with your flow
  • POWER PLATFORM SERVICES
Menu

How to find Excel rows missing in SharePoint list (Power Automate)

Posted on March 29, 2023March 30, 2023 by Tom

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

Power Automate Excel rows missing SharePoint

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.

Power Automate Excel rows missing SharePoint

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!


πŸš€ Master Power Automate

Join 2,100+ professionals getting actionable Power Automate tutorials, solutions, cheat sheets & tips every week.

No spam. Unsubscribe anytime.

2 thoughts on “How to find Excel rows missing in SharePoint list (Power Automate)”

  1. Toni says:
    February 20, 2024 at 9:34 am

    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

    Reply
    1. Tom says:
      March 17, 2024 at 1:03 pm

      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.

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

πŸš€ Master Power Automate

Join 2,100+ professionals getting actionable Power Automate tutorials, solutions, cheat sheets & tips every week.

No spam. Unsubscribe anytime.

Still exchanging emails to get things approved?

Get the Approval Process solution and the Task Delegation App to skip the hard part and deploy an automated, fully functional approval solution on a SharePoint list in minutes! And then the next one, and the next one...

Approval Template Preview ✨ Learn more ✨

Turn ideas into flows with ease!

Grab the complete Power Automate Cheat Sheet Bundleβ€”everything you need to master expressions, SharePoint HTTP calls, and Graph API in Power Automate.

Cheat Sheet Bundle Preview ✨ Get the Cheat Sheets

Didn't find what you were looking for?
Need to adjust a solution to fit your needs?
Or would you just like to get an assistance from somebody with thousands of hours of experience with Power Automate?

Power Automate blogs worth visiting

Damien Bird
Dennis (Expiscornovus)
Paul Murana

©2025 Let's POWER Automate | Theme by SuperbThemes