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

Removing duplicate data

Hi all, 

 

Pain point: Every month, I am required to manually copy and paste some data from each class list in an Excel file into the consolidated list in another Excel file and am have found a way to automate this process by using Power Automate. 

 

My Power Automate workflow looks something like this: 

Recurrence: 1 month 

List rows present in a table (Excel online): I have linked my individual class list here 

Condition: (value in excel) does not contain (null)

        If yes: Add a row into a table: I have linked my consolidated class list here and matched the data accordingly to the individual class list. 

 

However, here comes the issue: 

Since this flow takes place every month, I see that there are some duplicate data in my consolidated sheet which comes about from prior data in the individual class sheet. Since the process recurs every month, existing data which have already been pasted in the consolidated sheet in the prior month, would be copied and pasted again in the next month. 

 

By any chance, does anyone have a solution for this? Thank you! 

 

 

In case you would like to view my data sets, they look something like this:

For Consolidated Sheet:

Register number

Full Name

First Name

Last Name

Class

Test 1

Test 2

Test 3

1

Amanda Seah

Amanda

Seah

1A

94

12

43

2

Allen Chua

Allen

Chua

1A

32

32

21

3

Alan Lee

Alan

Lee

1A

57

62

83

4

Alicia Teo

Alicia

Teo

1C

83

54

72

5

Aidan Woo

Aidan

Woo

1C

28

58

92

6

Apple Chew

Apple

Chew

1D

94

20

54

7

Aloysious Chew

Aloysious

Chew

1D

43

23

29

10

Benjamin Ong (pasted from class 1B list from last month)

Benjamin

Ong

1B

53

23

30

11

Bennett Chua (pasted from class 1B list from last month)

Bennett

Chua

1B

42

43

28

 

For 1B Class list sheet (that should be updated in the consolidated file):

Register number

Full Name

First Name

Last Name

Class

Test 1

Test 2

Test 3

 

10

 Benjamin Ong

Benjamin

Ong

1B

53

23

30

 11

 Bennett Chua

Bennett

Chua

1B

42

43

28

 

12

 

Brenda Tan

 Brenda

Tan 

1B 

 43

 23

 32

 13

 Boon Ang

Boon 

Ang 

 1B

 92

54 

43 

 14

 Berry Ho

 Berry

Ho 

 1B

 83

65 

12 

3 REPLIES 3
Dual Super User II
Dual Super User II

Hi @vicktan 

 

You can use arrays to compare if data already exists. A sample implementation of this for Dynamics 365 is shown here: https://powerusers.microsoft.com/t5/Power-Automate-Community-Blog/Delete-duplicate-records-items-fro...

 

You can replicate similarly for Excel. 

 

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Microsoft
Microsoft

Using Run Script action from Excel online connector would be the right approach, IMO. Since the input and output consists of Excel files, you'll need flexibility around adding adding new data, modify logic, format data, etc. Office Scripts allows you to write all your logic in the form of a script (TypeScript) and run that within Power Automate. 

 

See here for more details: https://docs.microsoft.com/office/dev/scripts/develop/power-automate-integration

Hi @yashag2255 , 

 

Thank you for your reply. Was looking through the post directed by you and it seems like the duplicates were called out manually. Is there any way that I can do so by having everything being automated (ie I do not have to find out which of the data were duplicated)? 

 

Thank you so much and I look forward to your reply! 

Helpful resources

Announcements
PP Bootcamp Carousel

Global Power Platform Bootcamp

Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.

secondImage

Power Platform Community Conference On Demand

Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!

MPA Community Blog

Power Automate Community Blog

Check out the community blog page where you can find valuable learning material from community and product team members!

Users online (4,036)