How to do sum of column(Total profit) by Region in excel file

[sondhi.misha]

Dummy data

sales Records.xlsx (17.7 KB)

Hi Team,

Please help me to do sum of column(Total profit) by Region in excel file. I am new to UiPath. Please refer the attached file.

[postwick (Paul Ostwick)]

Have you gone through the free training on the UiPath web site?

[sondhi.misha]

I am doing it. But I have not seen any similar example.

[sondhi.misha]

I need this use case of my current project. It will be great if you can help me.

[ppr (Peter Preuss)]

Give a try on

• Build datatable Activity | 2 Cols: Region, Total Profit | Varname: dtReport

• Assign Acitvity
  left side: dtReport
  right side:

(From d in YourDataTableVar.AsEnumerable
Group d by k=d(“Region”).toString.Trim into grp=Group
Let st = grp.Sum(Function (x) CDbl(x(Total Profit).toString.Trim)
Let ra = new Object(){k, st}
Select r = dtTarget.Rows.Add(ra)).CopyToDataTable

Depending on how the Total Profit Values are read in from Excel maybe we have to adopt this part accordingly

Also have a look here:

[HowTo] Overview on different options for grouping data and processing the groups

This HowTo introduces on the different options for grouping data from a datatable in order to process the grouped data. Introduction Grouping data and processing the grouped data is a common scenario e.g. when the grouped data is to aggregate like summing up, find maximum, get the average or concatening items. Lets have a look on following data: [grafik] A possible scenario could be: Create a report containing following information: the region code the sum of CaseCount per RegionCode t…

[sondhi.misha]

HI , I have performed below steps according your above. But I am getting <Expression of type ‘System.Data.DataTable’ is not queryable. Make sure you are not missing an assembly reference and/or namespace import for the LINQ provider. Main.xaml> error
Please refer the attached screenshot as reference
screenshot.docx (182.0 KB)
error.

Step 1 Activity name : Build Data Table
variable name : build_sales_records_DT
variable type : data table

Step 2 Assign activity
variable name : calculate_sales_record_DT
variable type : data table

calculate_sales_record_DT = (From d In build_sales_records_DT
Group d By d(“Region”).toString.Trim Into grp=Group
Let st = grp.Sum(Function (x) CDbl(x(“Total Profit”).toString.Trim))
Let ra = New Object(){k, st}
Select r = build_sales_records_DT.Rows.Add(ra)).CopyToDataTable

[ppr (Peter Preuss)]

@ppr

(From d in YourDataTableVar.AsEnumerable
Group d by d(“Region”).toString.Trim into grp=Group
Let st = grp.Sum(Function (x) CDbl(x(Total Profit).toString.Trim)
Let ra = new Object(){k, st}
Select r = dtTarget.Rows.Add(ra)).CopyToDataTable

have a look here:

build_sales_records_DT was used but
but build_sales_records_DT.AsEnumerable is needed

[sondhi.misha]

what is dtTarget in your query?

[ppr (Peter Preuss)]

Also

@ppr

Build datatable Activity | 2 Cols: Region, Total Profit | Varname: dtReport

here the 2nd empty datatable that later will be used is to create and will be another variable.

It will reflect the summary Region / Total Sum

[sondhi.misha]

I am still getting the error. I have attached my Main.xaml to make it easy for you to help me.
Main.xaml (9.8 KB)

Error ERROR Validation Error Compiler error(s) encountered processing expression “(From d In build_sales_records_DT.AsEnumerable
Group d By d(“Region”).toString.Trim Into grp=Group
Let st = grp.Sum(Function (x) CDbl(x(“Total Profit”).toString.Trim))
Let ra = New Object(){k, st}
Select r = dtTarget.Rows.Add(ra)).CopyToDataTable”.
‘AsEnumerable’ is not a member of ‘System.Data.DataTable’. Main.xaml

[ppr (Peter Preuss)]

do fixing like described here:

AsEnumerable is not a member of 'System.Data.Datatable'

Hi @lucas.mcgonagle EDIT 20.10 fixed this issue for newly created projects. You can fix it by adding the Assembly Reference manually in your xaml file: [image] Simply open the file in Notepad and create new line with this String: <AssemblyReference>System.Data.DataSetExtensions</AssemblyReference> Then reopen your project and the error should be gone. Please be careful while doing so and always keep a backup.

remove all unneded activities
open build datatable activity and also remove all lines (one is havein text in first column)

[sondhi.misha]

Still facing the same issue. I have attached my text file.
System.Data.DataSetExtensions

Main.xaml (9.9 KB)

[sondhi.misha]

After adding below line in Main.xml
System.Data.DataSetExtensions

[ppr (Peter Preuss)]

It was missing the added AssemblyReference System.Data.DataSetExtensions
Kindly note: this has to be done on XAML with an editor (e.g. notepad++) having file closed in UiPath

Also LINQ was to correct
Group d by **k=**d(“Region”).toString.Trim into grp=Group

Have a look here:
Main_V2.xaml (10.3 KB)

it is validating.

Remove all unneeded Activities