Summarize values in DT without for each loop

Hey guys, maybe someone can figure this out. I’ve got a datatable with a department names followed by 4 numbers( 5 columns total).

There are several of the same department name but with different numbers.

Now i want to take the department names(col1) that are the same and summarize the values from the four columns into one row.

So i end up with just one row per department with the sum of all four number columns.

Im doing this with a for each loop inside a for each now, but something tells me it can be done in a simpler way?test - Copy.xlsx (9.2 KB)

@thomasb82 Can you Check this Workflow :
GroupByAndSum.zip (8.6 KB)

hey man, thanks it works well. Now im just gonna understand how you did it :slight_smile:

1 Like

hey again, noticing that you use “ifnullorempty” in the script, is that cos one of the rows in 0 is empty? cos that was a mistake on my behalf, there should be no empty values there :slight_smile:

@thomasb82 Yes, It handles that error, if there is an empty value it considers it as 0, since While using Sum method it needs a number and not a Space or Null value.

If you’re very sure there will be no Null values or empty values, and if it just a number, then you can remove that validation which makes the code length very less and more readable

hm, would i leave the convert.todouble only then or how would i be written then? remove the bold text as below?

…(New Object() {grp(0)(0),grp.Sum(Function (r) if(String.IsNullOrEmpty(r(“Column1”).ToString) or String.IsNullOrWhiteSpace(r(“Column1”).ToString),0,Convert.ToDouble(r(“Column1”).ToString)))

@thomasb82 Replace the Statement in the Assign with this :

(From p In DT.AsEnumerable() Group By x= New With { Key.a =p.Item(“Column0”)} Into Grp = Group Select DT.Clone.LoadDataRow (New Object() {grp(0)(0),grp.Sum(Function ( r ) Convert.ToDouble(r(“Column1”).ToString)),grp.Sum(Function ( r )Convert.ToDouble(r(“Column2”).ToString)),grp.Sum(Function ( r ) Convert.ToDouble(r(“Column3”).ToString)),grp.Sum(Function ( r ) Convert.ToDouble(r(“Column4”).ToString))},False)).CopyToDataTable