LINQ Query for data table to remove duplicate rows and concatenate values

Help Activities
, , , , , ,
1

Hi,
Please refer the input and output datatables.

image
image745×311 10.5 KB

I need to remove duplicates based on Name, Number and Friend column.
Once the duplicate rows are removed, Concatenate the Friend and Description column into 1 row for the unique Name Column.

Thanks in advance

2

Hi @Tanmay_V_Chetule

Try this

image
image828×576 23.1 KB

(From row In DT.AsEnumerable()
              Group row By Name = row.Field(Of String)("Name"), Number = row.Field(Of Double)("Number")
              Into Group
              Select DT.Clone.Rows.Add(Name, Number, 
                  String.Join(",", Group.Select(Function(r) r.Field(Of String)("Friend")).Distinct()), 
                  String.Join(",", Group.Select(Function(r) r.Field(Of String)("Description"))))).CopyToDataTable()

Output:

image

Regards,

3
  • Read Range activity to read the input Excel file:
  • Input DataTable: dtInput
  • Assign activity to define the LINQ query:
uniqueRows = From row In dtInput.AsEnumerable()
             Group row By Name = row("Name"), Number = row("Number") Into Group
             Select dtInput.NewRow() With {
                 .Item("Name") = Name,
                 .Item("Number") = Number,
                 .Item("Friend") = String.Join(",", Group.Select(Function(r) r("Friend").ToString()).Distinct()),
                 .Item("Description") = String.Join(",", Group.Select(Function(r) r("Description").ToString()).Distinct())
             }
  • Assign activity to initialize the output DataTable:
dtOutput = dtInput.Clone()
  • For Each activity to loop through uniqueRows and add them to dtOutput:
For Each row In uniqueRows
    dtOutput.Rows.Add(row.ItemArray)
Next
  • Write Range activity to write dtOutput to an Excel file.