cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
heyjon
Frequent Visitor

Unique rows from one Excel spreadsheet to another

I have two Excel spreadsheets that I'd like to merge into one with two conditions:

  1. The resulting spreadsheet should only have unique rows
  2. In the event of duplicates (in 'job' column) the row from 'Temp' should overwrite/replace the row in the 'Primary'  

Here is an example of the two tables:

 

Incoming Temp table

jobruntime_in_minutesstatustimestamp
A27running13:30
B15done13:15
C20done12:29

 

Pre-existing Primary table

jobruntime_in_minutesstatustimestamp
A2running13:05
B5running13:05
C20done12:29

 

And here is what I'd like the primary table to look like after the flow completes:

 

Resulting Primary table

jobruntime_in_minutesstatustimestamp
A27running13:30
B15done13:15
C20done12:29

 

I came across this thread about using a 'for each' conditional to delete duplicates but it clears all my rows out:

https://powerusers.microsoft.com/t5/Building-Flows/Remove-duplicates-in-Excel-Table-or-Delete-all-ro...

 

I also read a blog entry on performing a UNION however I'm a bit of a newbie and unsure how to use it so the 'Temp' table always wins if there is a duplicate entry in the 'Primary' table:

https://flowaltdelete.ca/2020/06/03/power-automate-expression-union-return-unique-values/

 

Here is my flow so far:

 

Screenshot 2021-01-08 143859.png

1 ACCEPTED SOLUTION

Accepted Solutions
Jronash
Impactful Individual
Impactful Individual

Another way of phrasing your problem is that when you go through the rows in your temp table, you need to know whether they should be added to the primary tables as new rows, or whether they already exist and should be updated.

 

To do this, you need to search the Primary table to see if the Job already exists. 

 

  • Before your ForEach1 loop, do a "List rows present in a table" on your primary table.
  • Then, inside your ForEach2 loop, use a "Filter array" action to filter the rows in your primary table where the Job column equals the data from the current Job item in your ForEach2 loop
  • If any rows are returned, you can update them
  • If none are returned, add a new row

View solution in original post

1 REPLY 1
Jronash
Impactful Individual
Impactful Individual

Another way of phrasing your problem is that when you go through the rows in your temp table, you need to know whether they should be added to the primary tables as new rows, or whether they already exist and should be updated.

 

To do this, you need to search the Primary table to see if the Job already exists. 

 

  • Before your ForEach1 loop, do a "List rows present in a table" on your primary table.
  • Then, inside your ForEach2 loop, use a "Filter array" action to filter the rows in your primary table where the Job column equals the data from the current Job item in your ForEach2 loop
  • If any rows are returned, you can update them
  • If none are returned, add a new row

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

MPA Licensing.jpg

Ask your licensing questions at the Power Automate AMA!

Join Priya Kodukula and the licensing team, super users and MVPs to find answers to your questions on Power Automate licensing.

Users online (2,348)