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

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.

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

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

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

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:

1 Like

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

have a look here:

build_sales_records_DT was used but
but build_sales_records_DT.AsEnumerable is needed

what is dtTarget in your query?

Also

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

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

do fixing like described here:

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

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

Main.xaml (9.9 KB)

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

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)

grafik
it is validating.

Remove all unneeded Activities