Remove duplicate from DT and copy only unique records -- LINQ

Hi,
I am trying to achieve only unique records from the DT also when segregating duplicate records I want to get the last matched record not the first.
INPUT
image

OUTPUT
image

@ppr can you look on this once

Hey @indrajit.shah,

You can use this linq query,

first clone the datatable using assign activty

dtResult = dtInput.clone()

than use this query in assign activity

dtResult =

(From dt In dtInput.AsEnumerable
Group dt By Column3=d1(“Column3”) Into Group
Select Group.Last()).CopyToDataTable

Here dtInput is name of your Datatable and dtResult should be in To and Query should be inside Value box

Thank you.

But I have to see both the columns Column 2 and Column 3 with next matched/duplicate record and then take the last row.

@indrajit.shah
May we ask you to clear the requirements within a razor sharp format. Taking last is unsharp. Havin a look on the data could it be in that way:

  • Find groups based on Group keys: Column2, Column3
  • take from group the group member with latest date from Column1

Is this your requirement?

So the statement for this could look like:

(From d In dtData.AsEnumerable
Group d By k1=d("Column2").toString.trim, k2=d("Column3").toString.trimInto grp=Group
Let oldest = grp.OrderBy(Function (r) DateTime.ParseExact(r("Column1").toString.Trim,"dd-MM-yyyy", CultureInfo.InvariantCulture)).Last
Select dtResult.Rows.Add(oldest.ItemArray)).CopyToDataTable

About the dt clone, referencing Assembly Datasetextensions, importing System.Globalization as done as usually.

The date format in the datatable is the Format, which is to implement (not the visually presented in Excel). So just debug the data and inspect it by using a breakpoint

1 Like

hi @indrajit.shah,

try ths,

(From dt In dtInput.AsEnumerable
Group dt By Column2=d1(“Column2”),Column3=d1(“Column3”) Into Group
Select Group.Last()).CopyToDataTable

what is d1 here

d1 is acting in general like a local variable. But your question is valid as in the statement from above d1 is not defined and is maybe a typo. We would recommend to rename dt to d1.

[HowTo] LINQ (VB.Net) Learning Catalogue - Help / Something Else - UiPath Community Forum