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.

    Redefine the way you work
    with Zoho Workplace

      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


          Get started with Zoho Sign

          in a few quick steps!

          Download Help Guide





                       
                      Secure your business
                      communication with Zoho Mail


                      Mail on the move with
                      Zoho Mail mobile application

                        Stay on top of your schedule
                        at all times


                        Carry your calendar with you
                        Anytime, anywhere




                                Zoho Sign Resources

                                  Sign, Paperless!

                                  Sign and send business documents on the go!

                                  Get Started Now


                                      Zoho SalesIQ Resources



                                          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 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 ...
                                                                            Wherever you are is as good as
                                                                            your workplace

                                                                              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