Unpivot in Tableau Prep (Rows to Columns)

• By Joshua Milligan
• Posted in All, Calculations, Data Blending & Joining, Data Shape & Structure, Tableau Prep
• On April 23, 2018
• 19 Comments.

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!