Skip to main content

Notifications

Community site session details

Community site session details

Session Id : OF/hSeB1GQrCnDPZx/1k5y
Power Automate - Building Flows
Answered

Avoiding duplicates when adding row into table from one Excel table to another

Like (0) ShareShare
ReportReport
Posted on 22 Nov 2022 12:56:15 by 30

Hi, 
I'm having issues with duplicates with Add row Into table from one excel table to another. 

 

In table 1 I have 

 

IDStatusCustomer 
ID_1Approved Customer_1 
ID_2HandledCustomer_2 
ID_3Not HandledCustomer_3 

 

In table 2 I have 

 

IDStatusCustomerEmail Sent?
ID_1ApprovedCustomer_1Yes
ID_2HandledCustomer_2No

 

 

I need to update table 2 with the new row from table 1 without Writing over the two existing rows and without creating duplicates for the existing rows. 

 

The ID is unique to each row in both tables (It is the same ID). 

Table 1 is a sheet that is updated each day whilst table 2 is a sheet that we are using for a number of different processes calculations.  

 

So the updated table 2 should be: 

 

IDStatusCustomerEmail Sent?
ID_1Approved Customer_1Yes
ID_2HandledCustomer_2No
ID_3Not HandledCustomer_3 

 

I can't find a reliable way to do this. Any tips as to how to achieve this? 


 




Any help is greatly appreciated 

  • LE19 Profile Picture
    6 on 15 Mar 2024 at 07:35:15
    Re: Avoiding duplicates when adding row into table from one Excel table to another

    @grantjenkins I have figured it out, it looks like the _ and the . in the column name aren't being recognised. When I change the column Title in the Table 1, and re do the expression and then re run the flow. it works perfectly and pulling the mobile number in. I thought I put in as reply, maybe helpful for others. 

  • LE19 Profile Picture
    6 on 15 Mar 2024 at 07:20:15
    Re: Avoiding duplicates when adding row into table from one Excel table to another

    @grantjenkins I follow your flow, and it works for me however I have notice in my case the mobile number column where they are numbers, but the column format in both table are set as text, did not populate the data in my table 2. Could it be the expression use when matching the field from table 2 to table 1? 

    LE19_0-1710486830173.png

     

    Appreciate if you could advise what's wrong with my expression. Thank you.

     

  • thevaibhav Profile Picture
    9 on 23 Nov 2023 at 17:06:21
    Re: Avoiding duplicates when adding row into table from one Excel table to another

    This was actually helpful, thing is you need to keep both Enter Key and Enter Value fields blank and then click switch map to text mode, I was entering key value then I was clicking switch to text mode and then I was clueless 

  • thevaibhav Profile Picture
    9 on 23 Nov 2023 at 15:43:46
    Re: Avoiding duplicates when adding row into table from one Excel table to another

    Unable to process template language expressions for action 'Apply_to_each' at line '0' and column '0': 'The template language expression 'outputs('Filter_array')['body/value']' cannot be evaluated because property 'body/value' doesn't exist, available properties are 'body'. 

  • thevaibhav Profile Picture
    9 on 23 Nov 2023 at 14:32:45
    Re: Avoiding duplicates when adding row into table from one Excel table to another

    This is not working if Table 2 is empty or null or at initial state, please help out further

  • HSFO Profile Picture
    30 on 22 Nov 2022 at 16:11:37
    Re: Avoiding duplicates when adding row into table from one Excel table to another

    Thank you! That was it! Accepting @grantjenkins solution. 

    Thank you both! 

  • Ajinder31 Profile Picture
    530 on 22 Nov 2022 at 16:04:22
    Re: Avoiding duplicates when adding row into table from one Excel table to another

    Hi @HSFO , The mismatch seems to be the Select Action. Please switch to text mode and assign UTI value as done by @grantjenkins 

    Ajinder31_0-1669133050739.png

     

  • HSFO Profile Picture
    30 on 22 Nov 2022 at 15:15:26
    Re: Avoiding duplicates when adding row into table from one Excel table to another

    Thank you for the quick reply! 

    It worked on the first go, but unfortunately when running it again it started where the last flow ended and added a whole new set of rows. 

    In my sheet ID is UTI 

    help 5.PNG

    There might be some mistakes on my part here. Any suggestions as to why it failed? 

  • Verified answer
    grantjenkins Profile Picture
    11,059 Super User 2025 Season 1 on 22 Nov 2022 at 15:00:01
    Re: Avoiding duplicates when adding row into table from one Excel table to another

    Here's what I would do. Tested and working well. If you've got more than 256 rows in your Tables, then you'll also need to add some pagination - let me know and I can show you.

     

    Full flow below. I'll go into each of the actions.

     

    grantjenkins_0-1669128798272.png

     

    List rows present in a table (x 2) gets all the rows for each of the tables.

     

    Select extracts out an array of all the IDs from table 2.

    grantjenkins_1-1669128914640.png

     

    Filter array checks to see if the ID is contained in the output from Select (IDs)

    grantjenkins_2-1669128953676.png

     

    We then loop over each of the items in our Filter array and add them to the table.

    grantjenkins_3-1669128999809.png

     

    The expressions used are:

    items('Apply_to_each')?['ID']
    items('Apply_to_each')?['Status']
    items('Apply_to_each')?['Customer']

     

  • HSFO Profile Picture
    30 on 22 Nov 2022 at 14:43:05
    Re: Avoiding duplicates when adding row into table from one Excel table to another

    Hi. 
    The sheet will probably be updated once a day for all business days. So lets say 300 times a year. 

    I was considering using a trigger on changes in source file (table 1), but a scheduled trigger would also work as the update on table 1 would be in the morning. 

    I would love to see you solution! In the mean time I can try to build a flow based on your suggestion. Thank you so much for your reply.  

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Understanding Microsoft Agents - Introductory Session

Confused about how agents work across the Microsoft ecosystem? Register today!

Warren Belz – Community Spotlight

We are honored to recognize Warren Belz as our May 2025 Community…

Congratulations to the April Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 146,745 Most Valuable Professional

#2
RandyHayes Profile Picture

RandyHayes 76,287 Super User 2024 Season 1

#3
Pstork1 Profile Picture

Pstork1 66,091 Most Valuable Professional

Leaderboard