Brain Ache Week: Dashboard #2 International Migrants

by Neil Lord

Of all the crappy spreadsheet in all the world, Andy had to give us this monstrosity to challenge us. “It’ll be easy” he said…hmmmm we’ll see.

Here’s the spreadsheet:

An Excel users dream, a Tableau users worst nightmare!

A summary of this sh*tty file; the data starts way down in row 16, there are multiple headers for each column, there are aggregated columns mixed in with the line data, there are 56 columns and 281 rows of data. Oh and there are multiple worksheets that need to be imported! At this point i’m tempted to go home.

But fortunately for me I have Alteryx on hand to do the hard work; so here is a summary of what I did to this crap to turn it into this thing of beauty:

Making use of the Dynamic input tool allowed me to quickly solve the problem of importing multiple sheets from one workbook, I created a quick spreadsheet that defined which sheets I wanted to import from the Workbook, configured the Dynamic Input tool with my main Workbook and connected up my other spreadsheet with the sheet list. In my case I decided to treat the headers differently from the main import, so I made sure that the Dynamic import just imported the data and nothing else. Finally a quick select tool just to tidy up any blank columns that get imported.

There was an issue with one of the worksheets being slightly different to the other and so I had to import that one separately and then union the data together:

So far nothing to challenging…but wait, there is still the headers to deal with.

Now, this may not be the most efficient workflow and I’m sure there is many other ways to achieve the same result but this is the way it worked out for me and it works and that was the goal.

And this is what I ended up with:

A right mess! And I even regretted starting this at one point as I seemed to be getting further away from my goal rather than closer to it. The overall aim was to turn this:

into this:

And ultimately I achieved that…so yay for me!

A few more tidy up jobs and I was there and once I pushed that magic “run workflow” I had my desired output in under 3 seconds. If I had to do that same process in Excel, I think I would probably shoot myself in the face (or call in sick).

Finished? No!

Buzzing with my Alteryx success I decided that this wasn’t enough and went about importing more data from this crappy data source. Thankfully though this data was in a similar structure just with different headers. With a few magic copy & paste actions and a few adjustments to the workflow, I was there:

Not the prettiest but it works and delivers the right result, plus it’s better than my first attempt which was a complete shambles.

And here’s what all of the work turned into:



© 2022 The Information Lab Ltd. All rights reserved.