Pivotting data for easier use in Tableau

Leave a comment

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.

How do I run commands from Excel ?

Leave a comment

My first blog post – I started my business just over a year ago and always promised myself to blog about what I do. A year later I look at the website and it feels pretty empty, so hopefully from now on I can post more about what I do 😉👍

You may or may not know that I spend a lot of time in the Tableau Community Forums (a lot !!!), I also credit the Forums with one of the best ways to increase your Tableau knowledge by ….

  1. Checking questions that have been answered, and read through the answer; download the workbook; etc… to learn how to answer that question, AND
  2. Answering questions that don’t have an answer – even if you don’t answer the question correctly someone else will correct you.

BOTH options are a WIN – you learn more by participating in the Forums.

So today I have some spare time (it’s the weekend, it’s pouring rain outside and the Australian Formula1 Grand Prix broadcast is starting soon 🏎 ) and I notice this post on the Forums:

My initial reply was just a vanilla comment about maybe using a batch file or Python. Really I was just wanting to reply because the post was over a month old with no replies and everyone deserves a reply 😊 (hot tip: the Crows Nest is a great way to find these “unanswered posts” and there’s some tricky questions in there to challenge your Tableau knowledge).

But as soon as I posted my reply I thought “that would be really simple in Python” (I’ve never actually read Automate the boring stuff with Python but I love the title), so some quick google searches and I found enough bits to write some Python code. I created a simple spreadsheet to mimic the spreadsheet above (OK, so a quick “dir” and “dir” with an additional path will serve the purpose) …

Just a few simple commands that will work

And some Python code to read the spreadsheet, join the command and parameter together and get the output …

Very simple, quite a few opportunities to make it better if required

And voila, a script that you can run to do a list of any valid DOS command that exists in the spreadsheet (maybe I can use this in the future as well 😉)

Yes, the script is very simple and there’s a few places where it could be improved, but in less than 30 minutes the forum post is answered and I’ve learned a bit more about Python 😎

Download the zip file if you want the files as templates for improvement

And the forum post that started all this is here