DataTable - Remove Duplicate Row

[Cormac (Cormac White)]

Hello,

Is anyone familiar with Remove Duplicate Rows (from the DataTable Activities)? I am pulling a large data table from SharePoint but I want to delete rows that have the same code in a certain column. So for example I have 5 columns in my data table and a lot of rows. I want to delete duplicates that that are contained in one of the columns. Does that make sense? Can anyone help?

[AshwinS2 (Ashwin S)]

hi @Cormac
try rhis

Remove duplicate rows based on a value from a single data column

I want to remove duplicate rows from a datatable if a particular column1 value is duplicate in other rows. dtOutput=dtOutput.DefaultView.ToTable(True,“column1”) when I try this it removes all the columns as well, but I need the rest of the columns.

Thanks
Ashwin S

[lakshman (Ganta lakshman)]

@Cormac

Try below expression.

   newDT = inputDT.DefaultView.ToTable("True","Column1","Column2",...)

Where inputDT is your original input data.

[Cormac (Cormac White)]

Thanks for the response but I don’t want to delete entire columns I just want to delete entire rows where the same code is found in ‘Column 1’. Does that make sense?

[lakshman (Ganta lakshman)]

@Cormac

It won’t delete entire column. It will give all rows which have unique data.

[Cormac (Cormac White)]

OK. I see that it won’t delete the entire columns but it doesn’t seem to work for me. The problem is (i think) that the entire row is not a duplicate. There are duplicates in ‘column 1’. I want to delete the entire row if there are duplicate codes in ‘column 1’ even though column 2,3,4 etc may not be duplicate. So in the screen shot below I want to get rid of test 2 and test 3 (the entire rows) but keep test 1 in the data table

[lakshman (Ganta lakshman)]

@Cormac

Try this once.

    newDT = inputDT.Asenumerable().Gropyby(Function(a) a.Field(of string)(“ColumnName”)).Select(Function(b) b.First()).CopyToDatatable()

[Palaniyappan (Forum Leader)]

Hi
this expression could help you with
datatable = datatable.AsEnumerable().GroupBy(Function(a) a.Field(of string)(“yourcolumnnname”).ToString).Select(Function(b) b.First()).CopyToDatatable()

if you are not getting asenumberable then kindly have a view on this thread

AsEnumerable is not a member of 'System.Data.Datatable'

When trying to use the AsEnumerable() method on a data table, i receive a error stating that AsEnumerable is not a member of ‘System.Data.Datatable’. I’ve seen someone else with this problem and their solution was to use temporary data table activities in their project to load in the System.Data.DataSetExtension library. However this has not worked for me. Any help would be greatly appreciated. Thank you.

Cheers @Cormac

[Cormac (Cormac White)]

@Palaniyappan

datatable = datatable.AsEnumerable().GroupBy(Function(a) a.Field(of string)(“yourcolumnnname”).ToString).Select(Function(b) b.First()).CopyToDatatable()

I can’t get that to validate. Do you know where I am going wrong?

[Palaniyappan (Forum Leader)]

its CopyToDatatable() buddy at last
you were almost done
Cheers @Cormac

[Cormac (Cormac White)]

That worked! Thanks so much!