Concatenate column values where duplicates using linq

[qwerty1]

Hi, I could really use some help. I have a data table that I need to modify when there are duplicates in the ID column.

The data looks like this:

Number	Locale	ID	Date	Area
123	A	312	17/05/2024	UK
213	B	312	17/05/2024	UK
789	A	879	14/03/2024	US
986	B	879	14/03/2024	US
912	C	879	14/03/2024	US

Where there are duplicates i’d like to concatenate the value that is in the Locale column with the Number column. Once thats been updated, Locale needs to be updated to the letter J so the output looks like this:

Number	Locale	ID	Date	Area
A123 B214	J	312	17/05/2024	UK
A789 B986 C912	J	879	14/03/2024	US

Can this be done?
Thank you

[ashokkarale]

@qwerty1,

Follow this approach:

Full Solution:

Variables:

groupedData LINQ:

dtInput.AsEnumerable().GroupBy(Function(row) row("ID").ToString()).ToList()

Concat LINQ:

String.Join(" ", currentItem.Select(Function(row) row("Locale").ToString() & row("Number").ToString()))

Add Data Row–> Array Row:

New Object(){strValue,"J",currentItem.Key,currentItem.First()("Date"),currentItem.First()("Area")}

Input/Output:

This is a working solution. You can always make it more refine.

Thanks,
Ashok

[qwerty1]

Thats great, thank you! Would you be able to attach the xaml file please?

[ashokkarale]

@qwerty1,

Unable to share the files due to restrictions but you should be able to recreate it with the detailed screenshots and expressions shared. Do let me know if stuck somewhere.

[qwerty1]

Perfect! Thank you for your solution

[Ajay_Mishra (Ajay Mahendra Mishra)]

Hey @qwerty1

I was also working for your solution, I have a different approach with a single LinQ

LinQ:

dt_Output = (
 From row In dt_Input 
 Group row By id=row("ID").ToString().Trim(),datee = row("Date").ToString().Trim() ,area=row("Area").ToString().Trim() Into grp=Group 
 Let Number = String.Join(" ", grp.Select(Function(x) String.Concat(x("Locale").ToString(), x("Number").ToString))) 
 Let Locale = "J" 
 Let IDValue = id 
 Let DateValue = datee
 Let AreaValue = area
 Select dt_Output.Rows.Add({Number, Locale, IDValue, DateValue,AreaValue}.ToArray())
	 ).CopyToDataTable()

Screenshot for your reference:

Attaching .xaml for your reference!
Concatenate column values where duplicates using linq.xaml (11.7 KB)

Regards,
Ajay Mishra

[qwerty1]

Thank you! I try learn different approaches, so will try this as well.