Create Table and Create Pivot Table using specific columns

I have 6 columns in my excel but I want to create pivot table using column 1, 5 and 6 only.
I used ‘Create Table’ activity to convert normal range to Table. Later when I use ‘Create Pivot Table’ it creates pivot table with all 6 rows but i need only 3 rows in it. Not sure how can I add specific columns in UiPath activity? Aso how can I add some columns in ‘Row Labels’ and some in ’ Values’ section?

PivotTable

Can you provide the workflow with proprietary data replaced with dummy data?

Demo-table

My workflow is simple. I have this automatic excel report downloaded from system which has several columns in it. It is just a range. So in my workflow, I convert range into tables and generate pivot table from it.

I want pivot table for mainly SITEID, ITEMTYPE and TOTAL columns.

Please note, Actual report has more than 15 columns than this which I don’t want in pivot table.


Seems I cannot upload excel here so attached image of my demo excel.

I noticed ‘Create Pivot Table’ activity adds all the columns from Table and generate pivot table. There is no way I can add few columns only. Not sure how I can do that?

The Excel document you’re uploading may be too large. If you reduce the number of records for testing purposes, you should be able to upload it.

Also, can you provide a ZIP file containing your project (with anything proprietary removed)?

Cross-Charge.zip (1.7 MB)

Please find attached project with sample csv and excel inside.

I have skipped the report generation part as it’s our secure system. ‘gmv-per-month.csv’ report is added in zip file.
First sequence is - Open Oanda for currency conversion as report contains local currency which we need to convert to USD.
Second sequence is per site sales which I want to calculate using Pivot table. I just want 4 columns here - SITEID, Action, ItemType and Total (In USD).

Let me know if you need any further information.

Regards,
Adoshi.

Hi @adoshi,

What if you try to use Filter Data Table to create a new Datatable with only the columns that you want and then use Pivot in this new table.

Hope this helps =)

1 Like

@Schirru I have created filtered datatable (with only 4 columns) and gave new datatable name to it.
But how can I create pivot table from filtered datatable? Do I have to use ‘Create Table’ activity first as I don’t have table in my excel.

Regards,
Adoshi

@adoshi

Check this out, it might help you :point_down:

Write only the columns you need into the table from which you want to create the pivot table. If you use only those 3 columns, the pivot table will contain only the data you need. If you don’t need your rTotal datatable for anything else, you can use the Remove Columns activity to remove the ones you don’t want. If you still need it later in the workflow, you can create a new data table and insert the data from these column into it.

@Anthony_Humphries


I have updated my workflow by adding filter database but my question is how can I generate pivot table using filter database? I have to create table before that but my range is not fixed.
Is there a way I can create table using filtered database?

Regards,
Adoshi

@Anthony_Humphries I found a way to do it using VBScript but want to know how can I do it using filter database. Attaching the solution which worked for me.

image pivotMacro.txt (2.5 KB)

1 Like

@adoshi

Filter DataTable Activity, will be useful, if you want to look for a specific content or remove a row or a column… That’s what mostly does.

I did have once the same issue like you, i used VBScript to do the pivot.

Hope this clears things to you :slight_smile: