How to remove duplicates in merge data table to get unique values?

[LB34]

Hi All,

I am merging to data table, 1 data table is from early this morning with data in it,
I have another newer data table I just scraped
both have same column names
There is a good chance they will have the same data plus more in the newer data table
I can merge both tables, however I want to remove all duplicates, not just 1 set of duplicates
All I want to be left with is rows that had no duplicates.

Is there a way to do that?
I tried 'remove duplicate rows, which it did, just not the way I thought it would.

Any Assistance is greatly appreciated

e.g
DT1
Fred, John

DT2
Fred, John, Sam

I want to merge both DT1 and DT2 (this works fine), however then remove duplicates, so all I have is
DT3
Sam

[NIVED_NAMBIAR (NIVED N)]

Hi @LB34

U can try the below linq queries to get the uncommon rows between the datatables DT1 and DT2

Check the below link for linq query

How To Find Common/Uncommon Rows Between Two Datatables – In UiPath

Hope it helps you

Regards

Nived N

Happy Automation

[LB34]

Hi @NIVED_NAMBIAR

Thank you for the link,
I am getting an error:

When testing i can see DT1 and DT2 (DT1,DT2 are randon Var names) in my workflow have data, when I then get up to the assign I have:

dt_output = dt1.AsEnumerable().Except(dt2.AsEnumerable(),System.Data.DataRowComparer.Default).CopyToDataTable

I run all the way through until that assign using the same code as in the post. I did change the variable names to match my data tables, not sure why it would give me this error.

below is my 2 data tables, red indicates the names

[indrajit.shah (Shah)]

What’s the final output you are expecting?

If your expected output is -

Fred
John
Sam

then try the attached file, I have used two columns “Name” and “Age” and used GroupBy to get the unique records.

(From d In DT1.AsEnumerable()
Group d By nam= d("Name"), age=d("Age") Into grp=Group
Select grp.First()).CopyToDatatable()

_testIndra.xaml (9.1 KB)

Give it a try and do let us know the final result.

[NIVED_NAMBIAR (NIVED N)]

Hi @LB34 this error implies that there is no unique rows between DT1 ad DT2

[LB34]

Hi @indrajit.shah

that code combines both of my data tables and removes duplicates.
All I want to do after a merge is have the output display non-duplicate/unique rows.

e.g
DT1
Fred, John

DT2
Fred, John, Sam

I want to merge both DT1 and DT2 (this works), however then remove duplicates, so all I have is

DT3
Sam

I hope to remove all duplicates and just leave the unique rows or non-duplicates.
Is there a way to add onto your code so it only show that?

Thank you for your help

[prasath17 (Guru)]

@LB34 - refer this post . This is has everything you are looking for…And specifically look for Non dups.

[HowTo] - Find Duplicate / NonDuplicate rows - checking all columns - without explicit listing the colnames/index

Introduction In the majority of scenarios duplicated / Non duplicated rows can be identified by a few columns grace to the key column concept. In order to retrieve it, the group by approach helps It can be done e.g. with some nice components from Connect or LINQ https://www.tutorialsteacher.com/linq/linq-tutorials In some scenarios all duplicated / non duplicated rows should be detected by checking all columns. Setting up an approach without explicit listing all column names or column …

[indrajit.shah (Shah)]

Correct me if I am wrong.

• Do you want to get the unique records right?

Is merge necessary? What I meant is we can always get the results by comparing both DT and get the unique one from whatever DT we want.

DT2.AsEnumerable.Except(DT1.AsEnumerable, DataRowComparer.Default ).CopyToDataTable

[LB34]

@indrajit.shah

DT2.AsEnumerable.Except(DT1.AsEnumerable, DataRowComparer.Default ).CopyToDataTable

Hi @indrajit.shah

That was it, Thank you for that.

@prasath17
Thank you for that post as well, I had not seen that yet.

Appreciate the help thank you both

[Rick_Bale]

To remove duplicate values use

Advanced Filter

This is a tool that is built into Excel and located on the Data tab of the ribbon.

Advanced Filter has a checkbox labeled “Unique records only” that will remove duplicates and output a list of unique values to the specified range.

Here’s and instruction guide on how to remove duplicates to create a list of unique values with Advanced Filter:

Click the Advanced Filter button on the Data tab of the Ribbon.
Select the “Copy to another location” radio button.
Select the “List range”. The range/column that contains the duplicate values.
Select the “Copy to” range. The cell where the new list of unique values will be output to. Should be a blank column.
Click the “Unique records only” checkbox.
Click the OK button.
The Advanced Filter will paste the values of the unique items starting in the cell specified in the “Copy to” range.

Regards,
Rick Bale

[jeevith (Jeevith Hegde, Ph.D.)]

Hi @LB34,

So in short you are looking for a right join of two datatables Dt1 and Dt2. In UiPath the join datatable activity does not give options for Right Join (Dt1, Dt2). But there is a work around to achieve this consistently.

Approach:

Step 1. Assuming you have a single column DTs (Dt1 and Dt2), first you can use a Join Data Table activity. Here I do a left join because Dt2 is having the extra unique value, but you can adjust it as per your case. Saved this as JoinedDataTable.

Step 2. Make a clone of any one of your input DataTables here I clone Dt1. This is done so that we can get the headers and type of the items in the DataTable. Lets save it as OutputDataTable

Step 3. We use For each Row activity and loop through the JoinedDataTable. Here is the interesting part. Logically, we do not want any values which appeared in both Dt1 and Dt2 only unique values (Sam) so make an if condition where we check row("Name_1").ToString.Trim.ToLower.Length = 0 This “Name_1” column was one of the output columns from Join DataTable activity in Step 1.

If the condition is met, add a row to the OutputDataTable. Else do nothing.

I have tried to annotate the workflow. GettingRightJoinOfTwoDTs.xaml (14.7 KB)
The output is in your expected format.