Pivot

Pivot Table distributes data for easy consumption. It spreads out the data in long, winding tables by converting categories into columns. Pivot can be created by selecting the Column, Row, and Data fields. Here's a quick video on how to use the Pivot transform.

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 choose one of the aggregate functions. These functions can get you the sum, count, average, etc. depending upon the data type of the fields added.

The list of aggregate functions available for each data type is listed in the table below. Click here to know more about aggregate functions.

Data type	Functions	Advanced functions
Number	Normal	Normal Running total % of row % of row in group % of column % of column in group % of total Difference from % of difference from % of previous value Lookup value
	Running total
	Minimum
	Average
	Standard deviation
	Median
	Mode
	Percentile
	Variance
	Count
	Distinct count
Text	Count	Normal Running total % of row % of row in group % of column % of column in group % of total Difference from % of difference from % of previous value Lookup value
	Distinct count
List	Count	Normal Running total % of row % of row in group % of column % of column in group % of total Difference from % of difference from % of previous value Lookup value
	Distinct count
Map	Count	Normal Running total % of row % of row in group % of column % of column in group % of total Difference from % of difference from % of previous value Lookup value
	Distinct count
Boolean	Count	Normal Running total % of row % of row in group % of column % of column in group % of total Difference from % of difference from % of previous value Lookup value
	Distinct count
Date	Count	Normal Running total % of row % of row in group % of column % of column in group % of total Difference from % of difference from % of previous value Lookup value
	Maximum date
	Minimum date
	Distinct count	Year Quarter and year Month and year Week and year Full date Date & time Quarter Month Week Day of the week Day of the month Hour of day

Note: The functions for the list data type counts the values and t he 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.

Aggregate functions

You can add fields to the Data section and choose one of the aggregate functions. The aggregate functions can get you the sum, count, average, etc., depending upon the data type of the fields added in the Data section. The list of functions available for each data type is explained in the table below:

Function name	Description
Sum	Returns the sum of values from a numeric column.
Maximum	Return the maximum value from a numeric column.
Minimum	Returns the minimum value from a numeric column.
Average	Returns the average of all the values from a numeric column.
Standard deviation	Returns the standard deviation based on all values from a numeric column.
Median	Returns the middle value of the numeric column.
Mode	Returns the value that occurs the highest number of times in a numeric column.
Percentile	Returns the percentile of the column. The 50th percentile is configured by default. However, you can modify this value as you need.
Variance	Returns the variance of the numeric column.
Count	Returns the total number of values from a column.
Distinct count	Returns the total number of distinct values from a column.
Maximum date	Returns the maximum date from a date and date-time column.
Minimum date	Returns the minimum date value from a date and date-time column.

Advanced functions

For each basic function such as sum, maximum, minimum, etc., you can further choose advanced summary functions like, running total, % of row, % of previous value, lookup value, etc. Using these functions you can calculate the running total of sales over years or the difference in sales from the previous year etc.,

The advanced functions are explained in the table below:

Advanced function name	Description
Normal	Returns the value of the function applied over a numeric column. This option is selected by default.
Running total	Returns the sum of sequence of numbers as a running total based on the selected Base field.
% of row	Returns the percentage of the total for each row.
% of row in group	Returns the total value of each row within a group as a percentage.
% of column	Returns the percentage of the total for each column.
% of column in a group	Returns the total value of each row within a group as a percentage.
% of total	Returns the percentage of the total of all the data.
Difference from	Returns the difference from the previous value based on the selected Base field.
% of difference from	Returns the differences from the previous values as a percentage based on the Base field.
% of previous value	Returns the percentage of the previous value based on the selected Base field.
Lookup	Returns the value from any of the previous, next, first or last cell based on the Base field selected.

SEE ALSO

What are the other dataset transforms?

Know more about filter transform