How to convert a table to pivot table with sum of a particular column

[jamnanin (Naresh Jamnani)]

I have an excel file having date, department and amount spent by each department. I want to create a pivot table such that it has the sum of amount spent by each individual department and the total too. I have attached a sample file for clarity.Pivot Sample.xlsx (9.8 KB)

[MAHESH1 (MAHESHKUMAR JV)]

@jamnanin
You can group by based on DepartMent column.
For grouping and take the sum refer this post

How do I group by one column in the datatable and take the sum?

@jamnanin I will suggest you one more way try like this. Let us take your excel stored in a datatable dta First group the datatable based on ID by using below Query and assign it to dt1 dt1=(From p In dta.Select() Group p by ID=p.Item(“ID”).ToString Into Group Select Group(0)).ToArray.CopyToDataTable() Now take only two columns by using below query dt1=dt1.DefaultView.ToTable(False,“ID”,“Name”) Now Add one datacolumn to dt1 by using add datacolumn activity and give name Amount to that c…

creating new datatable after grouping and taking sum of each group.
Now to take the sum of that particular column refer the below post

Issue with the sum in excel

Hi, @Braki instead forloop you can use LINQ. use below code to get sum of the column Convert.ToString(BaseTotal.AsEnumerable().Sum(Function(row) row.Field(Of Double)(“Importes”))) for each.xaml (39.4 KB) use this xaml file @Braki Regards, Arivu

Regards,
Mahesh

[jamnanin (Naresh Jamnani)]

Sure! Thanks

[balupad14 (Balamurugan)]

Hi @jamnanin,
Take a look the below link. Can use single activity to do the group by

BalaReva.DataTable.Activities

Hi Friends. Below you have Documentation and Sample attachment for the “BalaReva.Datatable.Activities” You can find the Activities in DataTableExtensions NameSpace [image] Add Data Row Range It allows to add collection of datarow into DataTable. Sample File : AddDataRowRange.zip (7.8 KB) Remove Data Row Select It allows to remove the data rows based on the Select(Where) condition from DataTable. Sample File : RemoveDataRowSelect.zip (7.6 KB) Group By Aggregation(Sum,Count,Avg,Max,…

Regards
Balamurugan

[Rajtk7 (Rajan Thangavelu)]

Hello All,
I have build lots of Excel & PDF Activities which we use on a daily routine.
Activities are as follows : -

1. Pivot Table - Works with or without Excel Application Scope.
2. Filter Range / Data - Works with or without Excel Application Scope.
3. Find & Replace - Advanced Technology. (See the video)
4. Text to Columns - Fixed width & Delimiter.
5. Copy paste data - To same workbook or to other workbook without any format loss.
6. Remove Duplicates - Single / multiple columns.
7. Update Status in an Excel sheet after processing some invoices or any (Completed, Success, Closed, etc)
8. Clear contents - To delete / clear the data of a given column.(See the function lists in drop-down)
9. Excel Save as - To save the excel in different format (.xls to .xlsb OR .xlsm to .xls)
10. Cells Formatting - To set the columns in a required format (General, Text, Number or any custom format)
11. Export PDF to Excel.
12. Export Notepad to Excel.
13. Extract specific lines from Notepad and paste into an Excel.
14. Extract specific lines from PDF and paste into an Excel.

All activites under custom activities in : - UiPath Innovation RPA Hackathon 2021: Join the UiPath Innovation RPA Hackathon 2021 to showcase the work you are doing to make a difference in the world through automation that is solving real world issues. - Devpost
Kindly go through each of the activities…

[Haroon_Patel (HP)]

Hello @Rajtk7 ,

I am looking for the custom Pivot Table activity by which I can add what goes
in column/value and rows.

I get error : “No package exist with this id in source…”