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.
Last week, we looked at how to transpose a single column of data using Tableau Prep. In some instances, you’ll need to transpose multiple. Let’s look at an example.
Imagine you are working with shipment data and it looks like so:
A Shipment ID might show up on multiple rows if there were multiple items placed in that order.
Let’s say we would like to transpose the data so it looks like this:
Each item, weight and shipping cost gets broken out into its own column. This is the perfect sort of problem to throw at Tableau Prep.
The first thing we need to do is add a column to the data source.
This column “pivot” is the field we are going to use to unpivot the data in Tableau Prep. While we could technically use the “Item Name” field, that will result in us having messy data headers like “Red Socks” and “Blue Hat”. 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 Shipment ID is encountered, then the start counts over. Here’s the formula to create that calculation in Excel.
Similar multi-row formula calculations are available in other platforms as well (but unfortunately not in Tableau Prep...yet).
The first step in Prep (after inputting the data) is to transpose one of the fields. I started with shipping cost.
Notice several factors which are highlighted:
● The dropdown next to “Pivoted Fields” is set to “Rows to Columns”.
● The “Pivot” field is used under the Pivoted Fields section.
● The aggregate field for the new columns is “Shipping Cost”.
● The new columns were originally labeled “1”, “2”, “3” and have been renamed to “Shipping Cost 1”, Shipping Cost 2” and “Shipping Cost 3”.
Next, we can add an aggregate step to simplify the data so there is only one row per shipment ID.
We’ll repeat this same process (adding the Pivot and Aggregate tools) for the Weight field.
The approach to transposing the “Item Name” field is slightly different since it’s a string (rather than numeric) field.
The primary tweak is to set the aggregation for the aggregate field (Item Name) to MIN.
Additionally, the MIN aggregation should be used on the Item Name fields in the aggregate step as well.
Now the hardest part is done. Just a few steps left to tie this all together.
First, adding an aggregate step at the beginning of the flow that groups the dimensions only.
Next, join all four streams of the workflow together.
Lastly, remove the duplicate fields (that were joined on) and observe the results!
The results are now limited to three rows of data which is exactly where we’d like to be!