I need to remove duplicate rows from excel and add one particular column using a delimiter for duplicate rows


I have screenshots of input
output should be

Restaurant Name General Manager(GM) GM-Phone Number Branch Name
KFC Paleti Kiran Kumar 8246175498 Kurnool Road Branch,Nellore Bustand Branch
KFC Marpudi Johnson 9625364282 Old Market Branch,Kothapatnam Branch,NorthByepass Road Branch

Hi @sathish27

Use Assign activity

DtOutput |Variable Type -> DataTable = DtBuild.Clone

How about this expression?

(From d In DtBuild.AsEnumerable
Group d By k=d(1).toString.Trim Into grp = Group
Let nj = String.Join(",",grp.Select(Function (n) n(3).toString.Trim))
Let ra = New Object(){grp.First()(0),k,grp.First()(2),nj}
Select r = DtOutput.Rows.Add(ra)).CopyToDataTable

Regards
Gokul

Sample XAML file for the Reference @sathish27

GroupByLinq.xaml (8.4 KB)

Regards
Gokul

Hi @sathish27 ,

Maybe a Grouping on 3 Columns is necessary ? If so, you could check the below Expression :

OutputDT = (From r In DT.AsEnumerable()
Group By x= New With { Key .a =r(0).ToString,Key .b=r(1).ToString, Key .c = r(2).ToString} Into Grp = Group 
Let updtGrp = grp.First.ItemArray.Take(DT.Columns.Count-1).ToArray.Append(String.Join(",",grp.Select(Function(c) c(3).ToString))).ToArray
Select OutputDT.LoadDataRow (updtGrp,False)).CopyToDataTable

Here, DT is your Input datatable read from Excel sheet, OutputDT is a new Datatable.

Let us know if you are facing any issues or need a discussion on the Grouping part.