Tally Amount Column / Convert column strings to Decimal

Help
1

Hi folks,

I have gathered data from an Excel worksheet to a data table. This contains four columns, one of which is “Amount”. The goal is to consolidate the amount values in the table, based on the values found in the other cells of the row. These amounts are monetary amounts, e.g. 123.00.
So if there are three rows containing information of a certain customers transactions, a final datatable will show this customer only in one row, with the three Amount values tallied.

This is done using DataTable.Compute as outlined here: Consolidate Rows in Data Table

As the data is taken from Excel, I have converted the (currency) values in the “Amount” column from string to decimal using a loop through each row, and an assign activity as pictured (Image 1)

A new datatable is created consolidating the rows, where the values in the other three columns match (image 2). And a new “Amount” column is added to this new datatable using AddDataColumn, with Type of System.Decimal

Lastly, we loop through this new consolidated datatable, using datatable.compute, attempting to sum the amount columns based on a filter of one of the other columns. (Image 3)

image
image687×812 104 KB

However, this exception is thrown at this stage:
Invalid usage of aggregate function Sun() and type: Object.

Any assistance? I have struggled with various fixes over the past two days and I’ve reached a point of frustration.

2

Hi @Yoggington

Check this

DT.AsEnumerable.Sum(Function(x) Convert.ToDouble(x(“cost”).ToString.Trim) ).ToString

and where function
DT…AsEnumerable.Where(Function(x) x(“column”).ToString.Trim.Equals(“123”)).Sum(Function(x) Convert.ToDouble(x(“column”).ToString.Trim) ).ToString

3

Thank you for your promptness @AshwinS2

However, I’m a bit confused by your response. At which points in the process are these utilised? And which pieces are variables I need to reference.

I am using the following;
dt_SalesReportData …(original datatable)
dt_Consolidated …(new consolidated datatable)
“Amount” …(Column name which is to be tallied)
“Sell-to Customer” …(Column name which is key for combining rows)