Removing duplicate records in datatable

(0) Share

Report

Posted on by GanCW

237

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?

Categories:

Power Automate Desktop

I have the same question (0)

All responses (10)

Answers (1)

Verified answer

miketran13 720 on at

Like (3)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
GanCW 237 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
MichaelAnnis 5,727 Moderator on at

Like (0)

Report
Copy link

Link copied!

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!

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

@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

Was this reply helpful? Yes No
MichaelAnnis 5,727 Moderator on at

Like (1)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
pmascal 4 on at

Like (1)

Report
Copy link

Link copied!

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. Include column names

Step 3: Read text from file (Not CSV File), and store contents as a list

Step 4: Remove Duplicates from List

Step 5: Join Text

Step 7: Write text to file

Step 8: Read from CSV File, First line contains column names is true

Was this reply helpful? Yes No
Agnius Bartninkas Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No