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.
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.