You know you have a great piece of software when you can use its paradigm to come up with workarounds to features that don’t yet exist. Such is the case with Tableau Prep (formerly Project Maestro). I recently needed to unpivot some data and realized, the Tableau devs are truly miracle workers!
Unpivot Data – Rows to Columns
What do I mean by “unpivot”? The term is actually used in different ways in different software packages, but here I mean taking rows of data and translating or transposing them to columns. So, if my data looks like this:
then, I want to take the list of metrics and have a column for each, like this:
Now, in many cases, having the taller, narrower data is actually better – if it’s all the same measure. But if you truly have different measures, then having an individual field or column for each one will make your life a lot easier. For example, if you only have the Value field, then what format do you give it? In some cases above, it’s a percent while in others it’s a whole number. In the second table, I can assign a different format to each field. Aggregations and calculations will also be quite a bit easier with the second table.
Unpivot data in Tableau Prep
Tableau Prep (Maestro) was just released and is an amazing tool, even in version 1! I truly hope to see an unpivot feature soon that allows everything to be done with a few mouse clicks and drag-and-drop. But until then, there is a way to accomplish it that is fairly painless.
(And then, of course, there’s the initial way I came up with in response to a question here, that is a bit more tedious. If you have more than a few columns, you wouldn’t want to do it that way. In fact, after some thought, I wouldn’t ever do it that way. But, here is a way that is simpler, less complicated, and easier to implement.)
Here’s the basic flow:
We’ll start with the original table from above:
We have multiple rows for each star ship – one for each metric we’re tracking. We’ve already considered some of the issues we’d face if we took this directly to Tableau.
The first step is to create a field for each metric (okay, it might be a little tedious if you have 50 metrics – but it’s mostly cut and paste). 
The idea is to capture the value for that row, if the metric matches the new column. Once you’ve created calculated fields for each metric, your data will look something like this:
You’ll only have values in the column for the row where the metric matches. You’ll have nulls everywhere else. And that’s perfect! Because aggregations ignore nulls, so we’ve got a perfect way to collapse the rows at the same time as we eliminate the nulls and keep only the values we want:
In this example, we group by the Ship and Registry in the aggregate step because differing values there define individual rows we want to keep. In real-life these might be ID fields, names, or keys the define the unique rows you want to keep. We’ll leave out the original Metric and Value fields and we can use any aggregation on the new columns we created because any aggregation will ignore the null values in the field and keep only the values you want. (Here we kept the default SUM for convenience of less mouse-clicks)
Once you have aggregation working, you’re done! You’ve successfully unpivoted data in Tableau Prep! Here’s the output:
And now, you’ll be a miracle worker!
Good one thanks for posting Jousha
Thank you!
Thanks for sharing! I’m testing out Prep and needed this workaround.
Question for you: I need to unpivot my table but instead of having numeric “Values,” I have a bunch of strings. When trying to use the Aggregate step per your instructions, it naturally aggregated my string values to a CNT instead.
I ended up using the Aggregate step but just added my new calculated fields into the Grouped Fields section (left pane) and then deleted/filtered out all the “Null” values from each calc field.
Is this Aggregate step even needed? Should I have just done this cleaning in another Clean step? I’m all open to suggestions/recommendations. Thanks again for the handy guide!
I should clarify — I thought I could get it to work, but turns out it’s not unpivoting as I was expecting.
I’m trying to unpivot a student course schedule table that has multiple records per student with columns such as:
Name | Course | Course Schedule Block | Description | Department | Teacher | etc.
I normally need to pivot data, but in this case I need to unpivot (per friend’s request) to ultimately end up with one row per student that say, has Course Schedule Block as the columns (A1, A2, B1, B2, etc.). They basically want to take this unpivoted structure and use it in a mail merge. Ugh!
At least I get some Tableau Prep practice. 🙂 Thanks for any help/advice.
Hi Gaylon,
Thank you for the feedback and questions! I think you’ll still want to use the aggregation step, but try changing the CNT to either MIN or MAX for the text fields. There may be some complexities that you are dealing with that I’m not following, so feel free to reach out with additional questions and clarification.
-Joshua
Didn’t know about the Min/Max trick with STR fields. Nice to know!
I’m not 100% complete, but I was able to use your tip to get the results out of the aggregate step. To complicate things, some students can have two different courses under the same schedule block (e.g., A1 = Fine Arts and another A1 = Mathematics). Therefore, I ended up adding two aggregate steps; one for MAX another for MIN. That seems to capture what I need.
I’m still playing around/figuring out the next step from there. If I UNION the two, then I end up with a stacked union where each student has two rows, but I’m trying to trim it down to one row per student. Either I need to somehow merge the schedule block fields (A1 from Table1 and A1 from Table2), or perhaps I should try a JOIN instead on student, and then merge the schedule block fields that way. Not sure.
I think the tricky part has be resolved by your min/max tip. Now I just need to get one row per student after merging the schedule block fields somehow.
Thanks again for help! So far I’m seeing the potential of Tableau Prep for someone like me who is still learning how to transform the data structure.
Hi Josh! Thanks for posting this. Is there a way to combine all the different rows into one? For example, if I want to make a scatter plot of Phaser Strength to Shield Strength, this method will give me a chart that looks like an “L”. How do I unpivot the data so I can chart columns against one another?
Awesome, Josh!
I have used your technique to mash multiple slices of end of month transnational data to determine the most current open transaction, and closed transaction for about 1.5MM records.
Now I have been asked to perform the same on a daily basis, to track changes on the process flow, when it has moved from one stage to another, etc. I have done this in the past on a smaller scale using a MySQL database and nested queries.
The way I am envisioning this is to create a ‘previous’ table that is the previous update (n-1), append a new table (n), determine all changes (new, closed, open – no change, and open – with change). So, where I am stuck in my development is how to calculate the change over time per process change.
Any thoughts or solutions you may know of that can point in the right direction?
Thanks in advance for any bones you can throw my way.
Cheers!
Damian,
I’m not entirely certain and a lot would depend on exactly what the data looks like. Assuming, at some point, you have each snapshot, then would it be possible to join them together (on period = period – 1)? Then you’d have the previous and current values in the same row where a comparison would be easier than if they were separate rows.
Joshua –
I do have a unique identifier for each invoice, but yes, you are correct in that I should think Join vs Union.. which is what I had alluded to above. By comparing the current status in one column to the previous status.. I can easily determine if a change has occurred and then follow a sequence to record. Brilliant!
Thanks and much appreciated!
Damian
Can we perform similar function in Tableau as like we do in Tiboc Spotfire i.e. Pivot Transformation. I know we can do similar so thought of thing in Tableau via SQL Custom Query and that will allow us to save the result as other intermediate table. But this feature is enabled only when data source connection is established using SQL or some other data connection, cloud etc. My question is can we do similar sort of operation of pivoting the data and then saving the grouped result in intermediate table when we have data source connection from Excel, CSV ot text file etc.
Please reply, its urgent request.
I’m not familiar enough with Spotfire to know which way they use the term “pivot” – I do know that it is used by some platforms (e.g. Tableau) to describe transforming columns to rows. That transformation is very easy in Tableau Prep as there is a Pivot step which supports standard pivots and coordinated pivots. And a standard pivot can also be done in Tableau Desktop.
If you mean transforming rows to columns (what Tableau would call “unpivot”), that’s more difficult. This post describes one possible approach that will work in many circumstances, but not necessarily all. I hope to see this built into Tableau Prep in future releases and anyone can add their support to that idea here: https://community.tableau.com/ideas/8707
I just ran into the fact that Tableau Prep 2018.3 lacks an unpivot or spread operation. This seems very odd since Hadley Wickham developed an entire grammar for data manipulation, showing that both pivot and unpivot are needed (in Hadley’s tidyr, these are called gather and spread).
Yeah, it’s definitely needed. I’m hopeful it will be released soon.
You’ve probably already seen this – but Prep 2019.1.1 now has the option to ‘unpivot’
You’ll see a new option in the header of the pivoted fields object – it’s a drop down that lets you select “columns to rows” (pivot – the default), and the new “rows to columns” (UN-pivot) option.
Release note is here: https://www.tableau.com/about/blog/2019/1/new-tableau-prep-pivot-rows-columns-and-add-descriptions-any-change-99937
James,
Thanks for adding this note! Yes, the unpivot function in Tableau Prep Builder is great!
James,
Thank you so much for pointing to the new version of Tableau Prep having “unpivot” option. I wanted to use this option for a dataset that I would unpivot on one field with multiple fields to aggregate, but I can’t find the option of dragging and dropping more than one field to the “Field to aggregate for new columns”. I would very much appreciate if you had suggestions.
Thanks,
Hi David! There is no option for aggregating multiple fields in an “unpivot” (pivot rows to columns). And it makes sense that there wouldn’t be, because each column (for each row) will only contain only one value. But you could restructure the data prior to the Pivot step to duplicate the rows such that the two measures could be stored in one field (with half of the rows labeled for the first measure and the other half for the second). Then, your pivot would only aggregate the single field, but you’d have both measures aggregated. You could then decide if you wanted to further restructure the result or if it met your needs.