Pivotting data for easier use in Tableau

I just answered this question on the Tableau Community Forums : https://community.tableau.com/message/1082243

It’s the typical problem of having the data created as a cross-tab (ie reporting style rather than database style), but with the added complexity of having 2 dimensions combined together (Country & Year in this case):

NOT really Tableau-friendly …. but all the right stuff is there

I decided to NOT use Alteryx for this one, although the solution would be just as simple πŸ™‚

I created this in Tableau Prep Builder in a few clicks:

The flow

I copied the data out of the forum post, the values were separated by tabs, so I called it data2pivot.tsv (because it’s not a CSV right ?)

So the steps are:

  • connect to the data: Prep Builder detects it’s a tsv file and sets up everything properly
  • pivot the 5 columns (except for Week)
  • Add a Step
    • Prep Builder recommended that I split the values (awesome, I was intending to write some code to do that):
    • But it defaults the Year datatype to numbers. I don’t like seeing my years with commas in the middle (aka 2,020) so I changed the datatype to String (aka Abc)
    • Rename the generated columns to Country and Year respectively
    • Remove the original field (before the split)
    • Rename the other pivotted field to Score
  • Output the result to TDE, Hyper or CSV

Open the output in Tableau Desktop and a few more clicks gets you this:

Voila in Tableau

So with a few minutes work we have the “not built for Tableau” data in the format it should be (3 separate dimensions and a measure) and a graph that the user can filter and format as required.

TECH DEBT:

  • I’ve built this to handle 5 pivotted fields only. Adding more fields means you need to change the pivot step so that the new fields are pivotted as well.

Leave a Reply

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