Add or Update a Row from One Excel to Another with Power Automate

Question

I am trying to build a flow that will update one excel from another. I have data that is extracted each morning and stored in an excel, and I want to be able to use that to update a master list of data, adding or updating rows where needed. I've figured out how to update the rows that already exist and how to add all rows to the table, but I can't figure out how to only add rows that don't exist yet. Help!

Answer 1

Dear Kayla,

Have you tried the flow above? Welcome to share any updates when you have time if you need further help on this issue.

Thanks for your effort and time.

Sincerely,

Cliff | Microsoft Community Moderator

Answer 2

Dear Kayla,

Welcome to the forum here.

As you may refer to set custom id or something else to check whether the row exists or not which may easily get the wrong result, the simplest way is to let the flow remove all the existing data on the master sheet and update it with the latest version of the sheet.

You can set the flow to run in a day to make it run smoothly.

Thanks for your effort and time.

Sincerely,

Cliff | Microsoft Community Moderator

Answer 3

Hi Cliff,

I considered that, but the volume of data will continue to grow over time and I would rather not have to export out a large file come six or seven months from now. I'm looking to export the data in two week intervals (it's Salesforce cases) and still keep the historical.

Answer 4

Dear Kayla,

Thanks for your updates and letting us know the current situation in your environment.

If you want to only add the items not existing or update the existing items, you may add a custom ID column such as ID in the master sheet and the subsheet.

You may add three conditions for the flow.

First let the flow to find the rows which have the same IDs. You can use the first Filter Array action and update the rows.

Next you can include two situations.

The first situation:

When the number of the rows in the sub sheet is greater than the number of the rows in the master sheet, find the rows not existing in the master sheet and add the rows.

First you can get the rows of the sub sheet and the master sheet by using the Select action and use length(body('Select')) and length(body('Select_2')) to get the number of the two sheets' rows. Finally use the Condition action to let the flow confirm the situation and use the Filter Array action to filter all rows which don't exist and add them to the master sheet.

Filter Array 2: int(item()?['ID']) is greater than outputs('Compose')

Compose3: items('Apply_to_each_3')?['column1']

Compose4: items('Apply_to_each_3')?['column2']

Compose5: items('Apply_to_each_3')?['ID']

The second situation:

When the number of the rows in the sub sheet is less than the number of the rows in the master sheet, find the rows which have the same IDs in the master sheet and update the rows from the sub sheet.

The actions added are similar to the first condition.

Result:

I suggest you give it a try in your environment and hope that the solution above helps!

Thanks for your effort and time.

Sincerely,

Cliff | Microsoft Community Moderator