Removing duplicate records in datatable

[GanCW]

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?

[miketran13 · Accepted solution]

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.

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

[GanCW]

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

[Anonymous]

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]

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!

[Anonymous]

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

[Anonymous]

@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

[MichaelAnnis]

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

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)

Delimited -> Next

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

Then click Finish

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

[Anonymous]

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.

[pmascal]

For anybody who finds this post in the future, I have a solution if you want a more efficient way to do this without having to

1. Loop through each DataRow within the DataTable and adding to a list delimited by a comma

2. manipulate the data afterwards in excel

Assume your DataTable is in a variable called "Data"

Step 1: Create a Temporary File

Step 2: Write Data Table to CSV File

Step 3: Read text from file as a list (Not CSV File)

Step 4: Remove Duplicates from List

Step 5: Join Text

Step 7: Write text to file

Step 8: Read from CSV File

Note: You will lose column header information, but this method is instant and much more efficient than anything else I've found.

[Agnius]

You could also write %DataTable.ColumnHeadersRow% to the text file before writing the data itself. This will help you retain the headers.