Group By DataTable C#

Help Activities
, , ,
1

Hi.

I’m working in an automation in C#. I have an excel file with data from invoices and users who have to approve it.

The DataTable contains this columns:
Number, Amount, Department, username.

For the same number, amount and department i have two or more different usernames.
For example:
Number, Amount, Department, username.
100, US$ 1424, XX, Rodrigo
100, US$ 1424, XX, John

I have to notify them that they need to approve the invoice.

I want to get a list where i can get this as result:
100, US$ 1424, XX, Rodrigo, John

Can you help me out?

Thanks.

2

Hi @Rodrigo_Martinez

Pls try this with your modify accordingly,

Use a LINQ query in an Assign activity:

(From row In dt.AsEnumerable()
Group row By key = New With {
Key .Number = row(“Number”),
Key .Amount = row(“Amount”),
Key .Department = row(“Department”)
} Into Group
Select resultTable.Rows.Add(key.Number, key.Amount, key.Department, String.Join(", ", Group.Select(Function(r) r(“username”).ToString())))).CopyToDataTable()

Make sure ResultTable has columns: Number, Amount, Department, Usernames.

Happy Automation

3

Hi,

Can you try the following?

image
image841×658 22.3 KB

dtResult = dtSrc.AsEnumerable().GroupBy(r =>  new { Number=  r["Number"].ToString(), Amount=r["Amount"].ToString(),Department=r["Department"].ToString() }).Select(g=>dtSrc.LoadDataRow(new[] {g.First()["Number"],g.First()["Amount"],g.First()["Department"],String.Join(",",g.Select(r => r["username"])) },false)).CopyToDataTable()

Sample
Sample20250529-1CS.zip (3.2 KB)

Regards,

1 Like
4

Hi @Rodrigo_Martinez you can use assign activity with below C# code.

var result = dataTable.AsEnumerable()
            .GroupBy(row => new
            {
                Number = row["Number"],
                Amount = row["Amount"],
                Department = row["Department"]
            })
            .Select(g => new
            {
                g.Key.Number,
                g.Key.Amount,
                g.Key.Department,
                Usernames = string.Join(", ", g.Select(r => r["username"].ToString()).Distinct())
            });

if you want to group by with only number you can remove columns in above code llike

 .GroupBy(row => new
            {
                Number = row["Number"]
            })

output result is of type data table.
Hope it helps.