• Unpivot in Tableau Prep (Rows to Columns)

    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!

    Given the right tools, Scotty could solve just about anything!

    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:

    Maestro Flow to Unpivot Data

    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:

    Aggregate to Unpivot Data in Maestro

    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:

    Unpivoted Data in Maestro

    And now, you’ll be a miracle worker!

    Scotty can unpivot in Tableau Prep


6 Responsesso far.

  1. Avi says:

    Good one thanks for posting Jousha

  2. Gaylon says:

    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!

    • Gaylon says:

      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.

      • Joshua Milligan says:

        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.

        • Gaylon says:

          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.

Leave a Reply

Your email address will not be published. Required fields are marked *