Pivot tables are a powerful tool for reshaping your data to unlock new analytical possibilities. While it might initially feel like you are making your data messier, pivoting makes it much easier for Tableau to work with your data.
When Do We Need to Pivot?
Tableau needs data to be structured in columns where each column holds one type of data. These columns can then be used on shelves or cards to create and modify visualisations.
A common scenario requiring pivoting is when a new column is added for each new date in a dataset. If you want dates to form an x-axis, they cannot work with this structure. You need one data field (column) to hold all the different dates and another column to hold all the relevant values for each of those dates.
Types of Pivoting
Tableau Prep offers two types of pivot transformations:
- Columns to Rows (Unpivoting) - Converting wide, short data into tall, narrow data
- Rows to Columns (Pivoting) - Converting tall, narrow data into wide, short data
Pivot Type 1: Columns to Rows
You will come across this type of pivot frequently, as it is common for people to organise data into a wide or cross-tabular format where we use the first row and column as headers and find data for Row 1 and Column 2 at their intersection.
Changing columns to rows is necessary when you want to reshape your data from a wide format (where multiple columns represent different variables/metrics such as dates or product type) to a long and narrow format (with a single column for the variables e.g. date, product type and another column for their corresponding values).
For example, here is some data for a company’s earnings by region and years in columns. Although this is easy for us as humans to look at and read, it becomes an issue for analysing it further in Tableau.
We should pivot these to have one column for "Year" followed by another column for "Sales”.
We can do that in Tableau Prep by loading the data in, and first adding a clean step to be able to clearly see the data and understand how you need to pivot it (Tip: it is good practice to immediately add a clean step after you load data in before you start cleaning).
Then add a new step and select ‘Pivot’. You will be presented with a list of all your measures on the left-hand side and an empty box to the right of it. This box is where you will drag the measures that you want to transform from columns into rows. Ensure that the dropdown box above the Pivoted Fields box is selected as 'Columns to Rows'.
Next, select the fields you want to pivot by holding down the Ctrl button and selecting the desired columns in the Fields section on the left-hand side and dragging them over to the Pivoted Fields box.
Finally, add another clean step to your prep flow to view the pivot and rename the default column headings. This leaves you with your newly pivoted dataset.
Pivot Type 2: Rows to Columns
Rows to Columns is, you guessed it, the opposite. Although not as common as Columns to Rows pivoting, it can be useful when multiple measures/dimensions are held in a single column, and allows you to separate these out into individual columns per measure/dimension.
For example, let's look at this mock dataset with employee performances on multiple rows by Metric. We want to change the shape of the data so that each employee is represented on a single row.
Once again, we can go into Tableau Prep and pivot the data so that it better fits the analysis we want to carry out. We want to be able to see each Employee and their scores per metric as one row.
Follow the same initial loading in and clean step as before, and again add a new step and select ‘Pivot’. Change the type of pivot to “Rows to Columns”, and drag the column that you want to become the new column headers into the top part of the middle pane, called “Pivoted Fields”. In this case it would be the “Metrics” field.
We also add the column that will form the values under those column headers to the ‘Field to aggregate for new columns’ box. For this example it would be 'Score'. I have aggregated by average as it will not change the values in this example. You may want to use a different kind of aggregation (e.g. Sum, Minimum, Maximum etc.) for your dataset.
Then you can add another clean step and make sure the data has been pivoted correctly for your intended analysis use.
Conclusion
To conclude, the main thing to remember when pivoting is that there should be one dimension per column to allow you to carry out effective analysis in Tableau. Remember: the goal is to structure your data in a way that makes sense to Tableau, not necessarily to human eyes. Have fun pivoting!