Sync Excel to SharePoint Without Duplicates using Power Automate

A complete, production-ready guide

---

What you’ll build

A flow that watches an Excel file, reads all rows in a table, and creates SharePoint list items only when the ID doesn’t already exist. It’s fast, reliable, and avoids the common pitfalls (e.g., broken select() expressions, duplicate rows, or initializing variables inside loops).

---

Solution architecture

Excel (table: test101)  ──► Power Automate
   columns: id, summary      1) Trigger: file modified
                             2) List Excel rows
SharePoint list: sync101     3) Get SP items
  accountname, summary       4) Build in-memory array of existing SP IDs
                             5) For each Excel row:
                                   if id NOT in array → Create item

---

Prerequisites

Excel

• Stored in OneDrive or SharePoint.

• Has a table named test101 with columns:

  • id (Text or Number)

  • summary (Text)

Tip: Format as Table in Excel → Table Design → Table Name = test101.

SharePoint list

• List name: sync101

• Columns:

  • accountname (Single line of text) → maps from Excel id

  • summary (Multiple lines of text)

Optional hard guard: In List settings → accountname → Enforce unique values = Yes.

---

Build the flow (step by step)

1) Trigger

When a file is created or modified (properties only)

• Point it to the folder that contains your Excel file.

2) Get file content

Get file content

• File Identifier: from the Trigger.

3) List rows present in a table

List rows present in a table

• Location/Library/File: same file as above (use Identifier).

• Table: test101.

4) Get items (SharePoint)

Get items

• Site Address: your site

• List Name: sync101

• No Filter Query (we’ll do a fast in-memory check).

5) Initialize array variable

Initialize variable

• Name: spIDs

• Type: Array

• Value: []

6) Apply to each (SharePoint items → build the array)

Apply to each

• Input (click fx):

  outputs('Get_items')?['body/value']
  

• Inside this loop → Append to array variable

  • Name: spIDs

  • Value:

    items('Apply_to_each')?['accountname']
    

⚠️ Your loop name may differ. If Power Automate names this loop something like Apply_to_each_SP, the items('…') reference must match exactly. Use the UI’s dynamic picker to avoid typos.

7) Apply to each (Excel rows)

Apply to each

• Input (click fx):

  outputs('List_rows_present_in_a_table')?['body/value']
  

8) Condition (does this Excel id already exist?)

Inside the Excel loop, add a Condition. In the left box click fx and paste:

contains(variables('spIDs'), string(items('Apply_to_each_2')?['id']))

• Operator: is equal to

• Right: false

This evaluates to true only when the Excel id is not already in SharePoint.
Again, adjust Apply_to_each_2 to your Excel loop’s actual name.

9) If Yes (ID not found) → Create item

Create item (SharePoint)

• Site Address: your site

• List Name: sync101

• Field mappings:

  • accountname:

    items('Apply_to_each_2')?['id']
    

  • summary:

    items('Apply_to_each_2')?['summary']
    

That’s it. The flow will only create items for new IDs.

---

Why this method works (and scales)

• No fragile select()/join()/compose gymnastics.

• Single SharePoint query to build an ID set; then O(1) membership check via contains() on the array.

• No variable initialization inside loops (compliant with Power Automate rules).

• Race-safe if you keep loop Concurrency off (see tips below).

---

Expressions reference (copy/paste)

• Loop SharePoint items input

  outputs('Get_items')?['body/value']
  

• Append each SharePoint ID

  items('Apply_to_each')?['accountname']
  

• Loop Excel rows input

  outputs('List_rows_present_in_a_table')?['body/value']
  

• Condition (ID not in SharePoint)

  contains(variables('spIDs'), string(items('Apply_to_each_2')?['id']))
  

  (Set operator = is equal to, right side = false.)

Adjust loop names (Apply_to_each, Apply_to_each_2) to what the designer assigns in your flow.

---

Troubleshooting

• “The template function ‘select’ is not defined or not valid.”
  Don’t use select() in Compose here; this design doesn't need Compose at all.

• Condition always returns false (creates duplicates)

  • Ensure the SharePoint column internal name is accountname.

  • Ensure Excel id is text-compatible; we cast with string(...).

  • Check that the Excel table name is exactly test101.

• Nothing is created

  • Confirm there are values in spIDs (peek the variable after the SP loop).

  • Make sure the Excel table actually has rows and the headers match (id, summary).

---

Performance & reliability tips

• Turn off concurrency on the outer Excel loop to avoid two parallel branches inserting the same ID at once.

• Limit columns returned by Get items using a view to just accountname to reduce payload.

• For very large lists, consider:

  • Enforcing Unique values on accountname in SharePoint (hard stop on duplicates).

  • Splitting processing into batches (pagination) or using OData $filter per row (if your environment’s internal names and types are consistent).

---

Variations

• Also update existing items: Add an Else branch to the Condition → Update item.

• Delete from SharePoint if removed from Excel: Build a reverse array from Excel IDs and compare against SharePoint.

---

Done!

You now have a robust Excel→SharePoint sync that never duplicates.
Want this as an importable .zip package or a screenshot walkthrough? I can provide both on request.