Pivot

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 CRM Training

      Create, share, and deliver

      beautiful slides from anywhere.

      Get Started Now





              Zoho CRM Training Programs

              Learn how to use the best tools for sales force automation and better customer engagement from Zoho's implementation specialists.

              Zoho CRM Training


                  Zoho TeamInbox Resources




                            Zoho DataPrep Resources

                              Zoho DataPrep Demo

                              Get a personalized demo or POC

                              REGISTER NOW


                                Design. Discuss. Deliver.

                                Create visually engaging stories with Zoho Show.

                                Get Started Now











                                                      • Related Articles

                                                      • Dataset operations

                                                        Dataset operations consists of combining and transforming datasets to suit your needs. You can perform the following operations in a dataset. Deduplicate Row-wise Column-wise Join Append Pivot Unpivot Calculated columns Data retention Derive dataset ...
                                                      • 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. ...

                                                      Resources

                                                      Videos

                                                      Watch comprehensive videos on features and other important topics that will help you master Zoho CRM.



                                                      eBooks

                                                      Download free eBooks and access a range of topics to get deeper insight on successfully using Zoho CRM.



                                                      Webinars

                                                      Sign up for our webinars and learn the Zoho CRM basics, from customization to sales force automation and more.



                                                      CRM Tips

                                                      Make the most of Zoho CRM with these useful tips.



                                                        Zoho Show Resources