Chapter 13. Pivoting Columns to Rows
I grew up in the ’90s, so when I hear the word “pivot,” the image that instantly pops into my brain is the Friends characters trying to get Ross’s sofa up the winding apartment staircase.
We first looked at pivoting data (not sofas in stairwells) in Chapter 4. This chapter will build on that discussion, looking at when and how to pivot columns into rows of data in Prep Builder. This type of data transition is also referred to as transposing.
When to Pivot in Tableau Prep Builder
Tableau Desktop needs the data to be in structured columns. Each column can hold one type of data and can be used on either Desktop shelves or cards to alter the view onscreen. Accordingly, a common use case for pivoting is when you are adding a new column for each new date in a data set (Figure 13-1).
Figure 13-1. Adding a new column per month to the data set
The data structure in Figure 13-2 isn’t great for two reasons. First, if you want the dates displayed on an x-axis timeline, you can’t do that with this structure, because you need one column to hold all the different dates and another column to hold all the relevant values for each of those dates. Second, if the file is updated, Tableau Desktop won’t automatically add the new column into your analysis. If the new data appeared as additional rows instead, it would be included in the view (as long as Desktop is able to read it). Figure 13-2 shows the view in Tableau Desktop of the data from Figure 13-1 in its current structure.
Figure 13-2. Building a timeline using the data from Figure 13-1
Using Measure Names and Measure Values is more complicated than just dragging a single date field to the Columns shelf in Desktop. We need to pivot this data.
There are two types of pivot in Tableau to select from:
-
Columns to rows
-
Rows to columns (discussed in Chapter 14)
How to Pivot Columns to Rows
To resolve the date situation, we need to change the columns into rows of data instead. After adding a Pivot step, select the Columns to Rows pivot option as shown in Figure 13-3.
Figure 13-3. Pivot options
The equivalent in Excel is to transform Table A into Table B (Figure 13-4).
Figure 13-4. Example of transformation of Table A to Table B
Let’s look at how to achieve this result in Prep with the Pivot step.
-
Add the Pivot step. All the fields (columns) in your data set will be listed in the left-hand pane (Figure 13-5).
Figure 13-5. All the data fields in the data set appear in the left-hand pane
-
Select the fields you want to pivot from columns to rows of data. You can use the Ctrl key (or Command for Mac users) to select multiple fields. After selecting the fields, drag them to the middle pane as shown in Figure 13-6.
Figure 13-6. Dragging the date fields to be pivoted
-
You should see your pivoted data in the bottom-right-hand corner of the screen (Figure 13-7).
Figure 13-7. Viewing the pivoted date fields
-
Rename your columns to match your data by double-clicking the field name (Figure 13-8).
Figure 13-8. Renaming a field in the Profile pane
Now that the data is reshaped, it’s much easier to visualize the data. As you can see in Figure 13-9, there are a lot fewer data fields in this view than in Figure 13-2 and no need to use Measure Names or Measure Values in Tableau Desktop.
Figure 13-9. Visualizing the pivoted data
When new data is added to the restructured data set, the chart will automatically update once you run the Prep flow again. Not only that, but all the rich date-related functionality in Tableau Desktop is now available to you too (Figure 13-10). This allows you to conduct a deeper and richer analysis.
