cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GanCW
Resolver II
Resolver II

Removing duplicate records in datatable

I have a datatable loaded from Excel table with the following records

 

  Col-1  Col-2 Col-3
1 Apple  USA   A
2 Orange China A
3 Apple  USA   B
4 Apple  China A
5 Pear   Japan A
6 Pear   Japan B

 

and i would like to create a new table (or list of lists) with unique value of Col-1 and Col-2

  Col-1   Col 2

1 Apple   USA
2 Orange  China
3 Apple   China
4 Pear    Japan

 

My initial thought is to load this as list of lists and use the 'Remove Duplicate Item from List' action to remove the duplicate records but this doesn't work.

 

Does anyone has suggestion on how best to accomplish this other than the brute force iterative method?

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
miketran13
Solution Specialist
Solution Specialist

Hello,

 

I would do these steps, so that you can have a list of data you want, then you can just convert it to a datatable or whatever you want.

 

listdata.png

Thanks and hope it can help you. 

Mike

---------------------------------

Did I answer your question? Please consider to Mark my post as a solution! to guide others

View solution in original post

8 REPLIES 8
miketran13
Solution Specialist
Solution Specialist

Hello,

 

I would do these steps, so that you can have a list of data you want, then you can just convert it to a datatable or whatever you want.

 

listdata.png

Thanks and hope it can help you. 

Mike

---------------------------------

Did I answer your question? Please consider to Mark my post as a solution! to guide others

Hi Mike,

Thanks for the tips. Converting the list to text string does the trick. 

I need to write the table back into to Excel so I will just use Split text to extract the two values and write to separate cells.

 

Gan

 

 

 

Hello @miketran13 ,

 

 I have  a list in power automate desktop , after I performed actions like 'Remove duplicate items in the list'. I have to change the list values(generic value type) to data table (with data rows), the data coming from the action 'remove duplicate items in the list'. As I need to write the data back to a excel file. 

 

My list contains dynamic values , as every time it will be changing. not specific one's . So kindly suggest what format to use for converting list values to data table, as I need to write back again in to the excel file in the same flow

 

Kindly look into this .Thanks in advance.

MichaelAnnis
Super User
Super User

What if instead, you just originally read column 1 and column 2 only...then, Remove Duplicates would work.  It would shorten this work around down immensely.  If you still need all 3 columns as another list, that's fine, but you should be able to take just column 1 and column 2 from Excel, into a DataTable, remove duplicates, and write back to excel.

 

Best of Luck!

Thank you for the response @MichaelAnnis , but to remove duplicates from excel data(as it will be as data table). Need to make use of these actions (create list, add items to list, remove duplicates in list), like need to convert data table into list & after performing above actions ,then convert back to data table as I need to get in excel file , back again.

 

or is there any alternative method other than this , please brief it as I don't know other methods than the above mentioned

soumya_12
Helper V
Helper V

@MichaelAnnis , awaiting for your response. Kindly look into the above scenario I mentioned. Basically over writing the excel data , but based on above SQL table condition

I revert you back to what @miketran13 said; so you end up here:

MichaelAnnis_0-1631636343617.png

 

Then, write to Excel in A1 (new workbook or new worksheet).

Select Column A

Data -> Text to Columns (you can do all this through recorder)

MichaelAnnis_1-1631636457176.png

Delimited -> Next

MichaelAnnis_2-1631636494208.png

Check "Comma" only (it is usually tab by default, so uncheck tab and check comma)

Then click Finish

MichaelAnnis_3-1631636569098.png

 

This will leave the fruit in Column A and move the Country to Column B.

Thanks a lot @MichaelAnnis for following up on this. But to stop confusion & to be more clear I mentioned my scenario in a different thread , which was posted by me . As the scenario here is little different.

 

Sorry to create a confusion between here & there , I understand that. Kindly continue through this thread & even I'll follow up on the same -> Re: format to use for converting list values to da... - Power Platform Community (microsoft.com)

 

kindly look into the scenario I mentioned there on using SQL, please look into that , expecting quick response. Thanks in advance.

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.

Top Kudoed Authors
Users online (2,288)