Pivot
Pivot Table distributes data for easy consumption. It spreads out the data in long, winding tables by converting categories into columns. A pivot can be created by selecting the Column, Row, and Data fields.
To apply the pivot transform:
1. Click the Transform menu and select the Pivot option. The Pivot pane will slide open to view.
2. Select the fields that you want to convert to columns in the Columns section. Choose the fields to feature as rows in the Rows section and the fields with data in the Data section.
Note: You can only choose fields with numerical data in the Data section.
3. You can also select the Filters tab if you want to apply any filters. Using filters, you can selectively filter data based on the filter conditions applied over one or more columns. If you want to learn more about filters, click here.
4. Select the Sort tab if you want to rearrange the order of your columns and rows. The fields added under the Pivot tab are also shown in the Sort tab. You can sort the data by rows and by columns using the By rows and By columns sections.
You can sort data in ascending or descending order, or perform a custom sort by selecting the options in the By columns and By rows sections.
You can view a preview of the table by clicking the Preview button. Click Reset to set back to the default selection (Ascending).
For example, you can select the Ascending and Descending options in the Product category and Region fields in the By columns sections, and select the Ascending option in the Customer Name filter in the By rows section to sort the Sales Data. The data sorted will look like as shown in the preview below.
You can also customize the arrangement by selecting the Custom option. You can drag and drop or use the up and down arrows to arrange the rows and columns.
Note: The filter and sort functionalities are optional.
5. You can add fields to the Data section, and you can choose one of the aggregate functions. The functions can get you the sum, count, average, etc. depending upon the data type of the fields added. The list of functions available for each data type is given in the table below.
Datatype | Functions |
Number | Sum Maximum Minimum Average Standard deviation Median Mode Percentile Variance Count Distinct count |
Text | Count Distinct count |
Date | Count Distinct count Maximum date Minimum date |
List | Count Distinct count |
Map | Count Distinct count |
Note: The functions for the list data type counts the values and the functions for the map data type counts the keys.
6. Click the Preview button and DataPrep will show a live preview of the pivot transformation.
7. When two or more fields are selected in the Columns box, the column names are joined using a dash or hyphen (-).
8. In the new dataset, you can open the data source settings from the Ruleset pane and edit the pivot configuration.
9. Click Pivot to apply the pivot configuration to your dataset.
Zoho DataPrep Personalized Demo
If you'd like a personalized walk-through of our data preparation tool, please request a demo and we'll be happy to show you how to get the best out of Zoho DataPrep.
- Zoho Workerly Home
- Forums
- Connect With Us:
- Zoho Recruit Home
- Forums
- Connect With Us:
New to Zoho Forms?
New to Zoho Sign?
Zoho Sign Resources
Sign, Paperless!
Zoho SalesIQ Resources
New to Zoho TeamInbox?
Zoho TeamInbox Resources
New to Zoho ZeptoMail?
Zoho DataPrep Resources
Design. Discuss. Deliver.
New to Zoho Workerly?
New to Zoho Recruit?
New to Bigin?
New to Zoho CRM?
New to Zoho Projects?
New to Zoho Sprints?
New to Zoho Assist?
Related Articles
Dataset transforms
Dataset transform consists of combining and transforming datasets to suit your needs. You can apply the following transforms in a dataset. Deduplicate Row-wise Column-wise Join Append Pivot Unpivot Calculated columns Derive dataset Mark as personal ...User roles and permissions in Zoho DataPrep
Zoho DataPrep has roles for users based on their entity permissions. In other words, the user roles in DataPrep is based on the user's access to each entity. There are two other roles—Account admin and Organization admin—that are not entity-based. ...Zoho DataPrep Pricing FAQ
1. What are "rows processed"? A row can be defined in the context of a dataset. In simple terms, a dataset consists of rows (records) and columns (fields). Each row in a dataset represents a set of related data and has the same structure. For ...