A pivot table allows you to extract the significance from a large, detailed data set. You can pivot the data in your Microsoft Excel, text file, and Google Sheets data sources from crosstab format into columnar format to more easily perform analysis. Or, if you are not working with the types of data listed, you can use custom SQL as an alternative way to pivot your data in Tableau.
To explain pivot in tableau let’s take help of an example:
Suppose we have conducted a survey of the popularity/ preference of washing machines and the result is the excel below:
So, now the requirement is that we want to analyze the feedback of each washing machine on the basis of Agree, Disagree and Neutral in Tableau.
Let’s connect Tableau to the excel and move ahead with the visualization:
The above visualization is not satisfying the requirement as the visualization is showing the total number of a particular combination ( i.e the highlighted Tableau visualization is showing that there are 5 records where we have a Neutral & an agree feedback). This is definitely not what we want.
In such scenarios the need of Pivot arises. To pivot in Tableau, follow along:
After you have established you data source connection and have got the preview of the data; click on Manage Metadata pill to get the below visualization:
Pivot the table as below:
The pivot result will be like this:
The original fields in the data source are replaced with new fields called “Pivot field names” and “Pivot field values.” You can always rename the new pivot fields.
Finally, the Tableau Visualization which will state the individual feedback of the Washing Machines:
Few points to remember while working with Pivot:
At a glance: Working with pivots
- The Pivot option is available from the grid and metadata grid.
- All fields in the pivot must be from the same connection.
- Only one pivot is allowed per data source.
- Pivot fields can be used as the join key.
- The Pivot option cannot be used in calculated fields.
- Red fields in the view and fields with exclamation points in the Data pane: Because the original fields are replaced with new pivot fields, any references to the original fields in the view will no longer work. They cause fields to become red in the view or show a red exclamation point next to the field in the Data pane.
- Null values in the grid: If all of the original fields used in the pivot are removed, for example in an extract refresh, null values display in the pivot fields.