How to Prevent Power Automate from Copying Duplicate Excel Rows
Question details
The user needs to stop a scheduled Power Automate flow from repeatedly copying the same rows from a source Excel file into a destination table.
- Product
- Power Automate, Microsoft Excel
- Device & OS
- not provided
- Scenario
- Running a scheduled Power Automate flow to copy Excel rows based on a specific static status (e.g., 'Yes').
- Observed behavior
- The flow repeatedly copies the exact same Excel rows during every scheduled run because it fails to track which records have already been processed and transferred.
Ensure that your source and destination Excel tables are properly formatted with clear column headers, and confirm that your data is stored in standard Excel Tables (accessible via Insert > Table).
Use a Unique Key and Filter Query in Power Automate
Check if a row already exists in the destination table before creating a new one by comparing unique key identifiers.
This is the most robust method to prevent duplicates. By checking the destination table for an existing unique key before copying, you ensure that no identical records are created even if the flow runs multiple times.
In your source Excel file, ensure there is a column containing a unique identifier (like an ID number, invoice number, or combined text string) for every row.
In your Power Automate flow, right before the step where you create a new row, add a 'List rows present in a table' action pointing to your destination Excel table.
In the advanced options of the 'List rows' action, write a Filter Query such as `IDColumnName eq 'UniqueIDFromSource'`. This searches the destination table for the current source row's key.
Add a 'Condition' action and check if the length of the outputs from the 'List rows' step is equal to 0. You can use the expression `length(outputs('List_rows_present_in_a_table')?['body/value'])`.
Inside the 'If yes' branch (meaning no match was found), place your 'Add a row into a table' action to securely copy the row without duplicating.
Track Processed Records with a Status Column
Update the status of a row in the source table immediately after it is copied to prevent it from being picked up in future runs.
Use WPS Office as Your Powerful Data Management Alternative
While automated workflows require Microsoft's ecosystem, you can seamlessly handle, filter, and remove duplicates from your exported Excel data using WPS Office. It provides a lightweight, highly compatible environment for your daily spreadsheet tasks without the expensive subscription fees.
- 1. Download and Install: Download WPS Office for free from the official website and install it on your device.
- 2. Open Your Spreadsheets: Open your .xlsx files directly in WPS Spreadsheet with perfect formatting retention.
- 3. Manage Data Easily: Navigate to the Data tab to instantly use built-in 'Remove Duplicates' and 'Advanced Filter' features.
Frequently Asked Questions
Can I use Excel formulas to prevent Power Automate duplicates?
No. While formulas like =UNIQUE(FILTER(A2:D100,B2:B100="Yes")) can extract unique rows dynamically within the Excel worksheet itself, they do not prevent a scheduled Power Automate flow from reading and copying the original records multiple times. Duplicate prevention logic must be built directly into the flow.
Why is my Power Automate flow copying the same rows every day?
If your flow runs on a schedule and fetches data using a static condition (such as looking for rows where Status equals 'Yes'), it will pull those exact same rows every time it triggers. To fix this, you must either update the status to 'Copied' after transferring or verify if the record already exists in the destination table before copying.
How do I filter out empty rows in a Power Automate Excel flow?
You can prevent the flow from picking up empty rows by using a Filter Query in the 'List rows present in a table' action. Setting a query like `YourColumnName ne null` ensures that Power Automate only processes rows that contain actual data.
