Wow! I have been stupidly busy at work recently. A promotion has indirectly meant I’m doing three jobs at the moment, in addition to recruiting for staff, creating multi-million pound budgets with crazily complex models and dealing with day to day business as usual. I’m bushed, and haven’t had much time to fiddle about with Tableau.
My 100% run with #MakeoverMonday is under threat! I usually bang out a submission on Sunday but this week that has slipped and I still haven’t done it. The advantage of not submitting early is that you get to see what everyone else is up to. One viz this week was, for me, head and shoulders about the rest. I absolutely loved Michael Mixon‘s entry, and it has taught me new things.
His entry is linked here, with an image of the top section of the viz below:
Michael’s stuff is alway aesthetically pleasing, but in addition to marvelling at the look of the viz, I was keenest of all to understand the way in which he was able to aggregate up the countries to a continental level. The original dataset (available as week 40 here) contained countries, but not the continents in which they are based.
Now you could easily use the lasso tool in one of the map views to manually define that aggregation, but I wanted to see how Michael did it.
Incidentally, I really do feel that Tableau should have this parent:child relationship of continent:country automatically defined – maybe something for a future release.
I asked the question, and got the reply. Honestly this Tableau community is astonishing. I’m grateful to Michael for replying, and for everyone else I’ve pestered in the past few months. Every single time I have asked a question (and there have been lots of questions!), people have gone out of their way to respond, and that’s part of the reason I write these posts: to share my learning experiences.
Now I can recall a video from Andy Kriebel referring to Google Sheets, so I dug that out and refreshed my memory:
Now Google Sheets is a new thing to me, and a new thing to Tableau 10. At home, I use a MacBook Pro and I don’t have MS Office installed. As a result, until now, I’ve done zero data manipulation or blending with Tableau, as I haven’t had the option of adding or manipulating new data to complement existing data sets in the familiar environs of Excel. I did, however, have a Google account, so it’s time to dig deeper into Michael’s reply to see how easy it is to get grips with this new stuff.
First job was to Google “Google Sheets”, and I ended up here. A good start. Once entering my credentials I was ready to go. Michael’s post included the formula he’d entered to pull data from a site, so I opened another tab and navigated to that site. When you’re there, you can select separate links to continents, where the data is organised like this:
From Michael’s image, I can see that he’s imported data into separate tabs for each continent, and that seems a sensible thing to do, so I’ll replicate his process. Getting data into Google Sheets from websites is a walk in the park, and simply involves a four step process:
- Start the formula with =IMPORTHTML
- Whack in a bracket and input the web page url in speech marks – i.e. (“http://www.countrycallingcodes.com/iso-country-codes/africa-codes.php”
- Generally with Tableau, you’ll be importing tables, so after the web page url, enter ,”table”,
- Finally, you enter a numeric value to denote the number of the item on the page that you want to import. In this instance, it was (allegedly) the fourth item on the page that I wanted to import, so the full string ends up being:
So, in my terms:
Get stuff from this page. It’s the fourth table.
Now it isn’t the fourth table on the page, so I don’t know why that “,4” works for this example. But neither does Michael, so I don’t care! Generally the logic works. If not, just fiddle with that numeric value until you pull through the expected data.
Good. This is easy. I can rinse and repeat this process for each of the continents, simply updating the formula each time to reference the relevant web page. Once complete, I just renamed the file with an appropriate name, and fired up Tableau. Under the “To a server” section, you have the option of selecting Google Sheets:
Select that and then you’ll have to enter your Google account email address and password, before you have to authorise Tableau to access a bunch of stuff related to your Google account:
Once authorised, a screen appears listing all of your saved Google Sheets. Select the required file and hit Connect. Once I did that, I got this view in the Data Source pane:
Six sheets, as I created six tabs for six continents. Note the “New Union” option at the foot of the image. I recalled from Michael’s reply that he had unioned the sheets together, and whilst I’m wet behind the ears with a lot of technical jargon, my familiarity with stock MS Office tools (especially Excel and Access) means that I knew full well what unioning achieves. Essentially, it’s a means of appending like-formatted tables together into a single view. As I know that the structure of each of my sheets is the same in terms of fields, field order and data types, I can just stick them together.
How do you do that in Tableau? It is ridiculously easy. First, I dragged the “New Union” thingy into the pane containing “Drag sheets here”:
Once done, a new window appears and you’re invited to drag the required sheets into that window. I shift-selected my six sheets and dropped them in, and then selected OK:
Sneakily, the union includes the name of the Sheet in the table, so I end up with each of the countries from the six continents, each of which is tagged with their parent continent. Honestly, this was so much easier than I expected and it makes me realise what a great addition this Google Sheets integration is for Tableau 10.
Cool. So I have a new data source which I can now combine with the source #MakeoverMonday data to enable analysis at the level of continent, rather than country. Let’s figure out how that all hangs together. First, I added the Global Peace Index .tde as an additional data source:
When that was done, my data tab showed two sources:
OK. So now I need to join these tables together to pull continent through from the unioned Google Sheets.
Simple stuff. I chose a Left join as I want to retain all of the data from the #MakeoverMonday dataset. If a match wasn’t possible from the Country field in the Extract table to the “Country / Territory” field from the unioned data, then a null would be returned. To check if I have any mismatches, I just sorted the “Sheet” field from the unioned data in descending and then ascending order to see if I had any null values.
There weren’t any, so I know that the join has worked and there are no mis-matches. Then it’s just a case of starting to visualise the data! If there were mismatches, then it would just be a case of editing the relevant Google Sheet, reimporting the data, redoing the union and then validating the joins again. A nice, logical sequence of events.
So there you go. This is a post just skimming the surface of Google Sheets integration with Tableau. It’s dead easy, and was inspired simply by looking at a viz. That’s what #MakeoverMonday can inspire you to do, so thanks to Andy’s K and C, and to Michael Mixon for his input throughout this learning process.