DataTable Group By and Sum

[huang.kaijin (KJ Huang)]

Hi All,

I have a DataTable extracted from Excel which looks like this:

|Expense category|Cost|
|Traveling|$350.00|
|Food|$50.00|
|Traveling|$789.00|

I wish to group the data table by Expense category and Sum up the Cost.
Travelling - 1139
Food - 50

are the any means to group by Expense category and Sum up the Cost using DataTable.Select()?

[Anil_G (Anil Gorthi)]

Hi @huang.kaijin

Wlecome to the community

Please use like this

dtResults = (From dte In dtTable.AsEnumerable
Group dte By col1=dte(“expense category”).ToString.Trim Into Group
Select dtResults.Rows.Add({col1, Group.Sum(Function (x) Cdbl(x(“cost”).toString.Replace(“$”,””).Trim))})).CopyToDataTable

Dtresults is the final result use in assign on left and remaining on right
dtTable is the input datatable

Change the column names to exactly match your datatable

Cheers

[huang.kaijin (KJ Huang)]

Hi Anil_G
Thanks for the quick response.
I am getting Assign: Object reference not set to an instance of an Object

The code you given is as follows:

anything i might have missed out?

input datatable: ExpenseTable
output datatable: ExpenseTableProcessed
Column Names: Expense category and Cost

(From dte In ExpenseTable.AsEnumerable
Group dte By col1=dte(“Expense category”).ToString.Trim Into Group
Select ExpenseTableProcessed.Rows.Add({col1, Group.Sum(Function (x) CDbl(x(“Cost”).toString.Replace(“$”,””).Trim))})).CopyToDataTable

[Anil_G (Anil Gorthi)]

Hi @huang.kaijin

Please replace the inverted comma .I guess you directly copy pasted. with new .net there is a different inverted comma

and make sure there is data in the datatable

cheers

[huang.kaijin (KJ Huang)]

Hi Anil_G

I replaced the " s, it is still giving me the same issues.

I have also checked the scope of both input and output datatables, they are both in the same level.

Any other places to look at to debug?

thank you

[Anil_G (Anil Gorthi)]

@huang.kaijin

ExpenseTable
output datatable: ExpenseTableProcessed

Hi @huang.kaijin

please include an assign like below before group by step

ExpenseTableProcessed = ExpenseTable.Clone()

If you don’t need new datatable then you can use the same datatable name again as well

You are getting the error because the datatable is not initialized previously

cheers

[huang.kaijin (KJ Huang)]

Hi Anil, Cloning the DataTable worked.
Cheers.

Thank you for your help

[huang.kaijin (KJ Huang)]

Hi Anil_G.

can the code you provided be refined slightly?
UiPath is throwing an error saying:
Assign: Conversion from string “” to type ‘Double’ is not valid.

But i am getting the desired output to the datatable when debugging.

input dataTable:
[Expense category,Cost
Traveling,350.5
Food,50
Traveling,789.75
,
,
]

Output datatable:
[Expense category,Cost
Traveling,1140.25
Food,50
]

(i changed the decimals for testing to see if theres any issue with Excel not returning the values as Double)

[Anil_G (Anil Gorthi)]

Hi @huang.kaijin

Is the datatype of cost double in your datatable?

Or are there empty costs in datatable?

Try this

(From dte In ExpenseTable.AsEnumerable
Group dte By col1=dte(“Expense category”).ToString.Trim Into Group
Select ExpenseTableProcessed.Rows.Add({col1, Group.Sum(Function (x) if(IsNumeric(x(“Cost”).toString.Replace(“$”,””).Trim) ,CDbl(x(“Cost”).toString.Replace(“$”,””).Trim,0)))})).CopyToDataTable

Cheers

[huang.kaijin (KJ Huang)]

Hi Anil_G

I managed to get it to work, there were blank rows in the input DataTable.
added a filter dataTable to remove all blank rows worked after that.

Many thanks for your help