Power Platform Community Forum Thread Details

Hello,

I receive the same email each morning with a report via an excel email attachment. This attachment consists of a list of "open" items - one item per row of data. As these items are "worked" they fall off of the report as they are closed. The end goal is for there to be a power automate flow that extracts the data each day and adds the data to a master file. However, I also need the flow to look for data that has already been previously copied. I have a unique "item number" as a data point for each item.

I am OK with the master file being an excel file or sharepoint list.

Any help would be appreciated.

Categories:

Building flows

To automate this process, Power Automate can be used to extract data from the daily Excel attachment and update a master dataset while avoiding duplicates based on the unique item number.

Start by ensuring that the Excel file in the email attachment contains a properly formatted table. Power Automate’s Excel Online (Business) connector requires data to be in a table for reliable access. If the file is not structured as a table, you can use Office Scripts to convert the data range into a table once the file is saved to SharePoint or OneDrive.

Once the file is in the correct format, the flow should trigger when the email arrives and save the attachment to a specific location in SharePoint or OneDrive. From there, use the “List rows present in a table” action to read data from both the incoming file and the existing master file. For each row in the new file, check whether the item number already exists in the master dataset. If it does not, append the row to the master file.

The master dataset can be maintained in an Excel file with a defined table stored in SharePoint, which is suitable for low to moderate volumes. If you need more structure or plan to expand the process, storing the data in a SharePoint list may provide better long-term maintainability and performance.

Be sure to account for data consistency between the incoming files and the master file. Differences in formatting, extra spaces, or inconsistent casing can lead to inaccurate comparisons and duplicate entries.

Hope this helps.