By: Eric Parker
Eric lives in Seattle and has been teaching Tableau and Alteryx for 5 years. He's helped thousands of students solve their most pressing problems. If you have a question, feel free to reach out to him directly via email.
(Please see this post for an update on how to do this in 2021)
—-
In June, 2018 I wrote a blog post about three things Tableau Prep couldn’t do. One of those was the ability to transpose (unpivot) data. That is no longer the case. Tableau Prep Builder now gives you the capability to transpose rows of data into columns. Let’s look at an example.
Imagine you have a data set that looks like so:
Each row of data represents an employee and a specific job title. When the employee has a new title, a new row of data is created.
Now imagine we want to better understand employee career progressions in our company so we want the data to look like this:
Tableau Prep can help us achieve that.
The first thing we need to do is add a calculated column to the data source.
This column “Pivot Field” is the field we are going to use to unpivot the data in Tableau Prep. While we could technically use the “Title” field, that will result in us having messy data headers like “HR Coordinator” and “Vice President”. Better to use something generic like a numeric numbering system.
Notice that the count starts and 1 and adds a 1 to each row of data until a new Employee ID is encountered, then the start counts over. Thanks to my smart sister, here’s the formula to create that calculation in Excel.
At this point, it is not possible to create a field like that in Tableau Prep. Hopefully multi-row calculations like that are on the near horizon.
The first step after loading the data table into Tableau Prep is to add a Pivot step.
Notice several highlighted factors:
● The dropdown next to “Pivoted Fields” is set to “Rows to Columns”.
● “Pivot Field” is used as the value which Tableau Prep will pivot on.
● The aggregate field for the new columns is “Title” and the aggregation is set to MIN (so a text value is returned).
Next we can rename the new columns “1”, “2” and “3” as “Job Title 1”, “Job Title 2” and “Job Title 3”
Next, we can add an aggregate step to simplify the data so there is only one row per Employee ID.
Notice that the values are grouped on Employee ID and Employee Name and the Job Title fields are aggregated with the MIN function (once again to return text).
At this point the data is ready for output. Notice how the values are now unpivoted so there is only a single row of data per employee.
Want help with your own data preparation problems? Sign up for an office hour and we’ll help you sort them out!