Shaping Data for Use with Tableau

Start on the right foot by properly preparing data for Tableau

The single biggest barrier to Tableau adoption is properly shaping the data. Ryan shares his personal Tableau story and the role of migrating existing Excel reports to Tableau played in his journey.

Hi. This is Ryan with Playfair Data TV. And in this video, I’m going to show you the ideal way to lay data out for use with Tableau. I like to share this very early on the Fundamentals learning path because I view this as the number one barrier to Tableau adoption.

I’ve seen this play out over and over again during my career as an analyst and a consultant. But it also happens to be my own story. So I’ll share it from my own perspective.

At my very first job, I worked at a very small marketing agency where only five people worked. There were three of us analysts on the team. And like many companies, we had been doing all of our reporting in Microsoft Excel.

Well, one day, the boss walked in. And she said, I’ve heard of this tool Tableau. Can you guys take a shot at migrating all of our existing Excel reports over to Tableau?

Well, you open Tableau. And if you’ve ever used Tableau before, you’ll see that the very first data connection is Microsoft Excel. So you think, fantastic. This is going to be so easy. I’m just going to choose Excel, find that old Excel report, open it in Tableau.

This is going to work like magic. Maybe we get to go home early today. But as you know, that is rarely the case. That is not what ends up happening.

What happens is Tableau misclassifies a lot of the data fields. You don’t know how to drag and drop anything. You think everything’s broken. And people immediately revert back to that familiarity of Microsoft Excel.

Well, I’m going to show you exactly why that situation happens. It’s because, most of the time, your existing Excel reports are laid out something like this, where I want to point out a couple of things about this data layout.

You may have it– so first of all, it’s in kind of a horizontal structure. We’ve got, in an effort to maybe see some trending, even in this tabular format, we’ve got our dates going left to right. Those dates may or may not even be in an actual date format. Like you see here, that Q1, Q2, Q3, Q4, which is meant to represent quarters, is going to be interpreted as a data type of string in Tableau because it starts with a letter.

We’ve then got our measures going down that first column. And this could even be more problematic if we had subtotals on each of our columns or totals at the ends of each of our rows. All of these things create challenges for how Tableau interprets a data source, and then allows you to start using it to slice and dice data and get insights from that data.

I don’t have very many blanket statements here at Playfair Data TV. But this is probably about 95%. This is probably as close as I get. I would say, particularly when you’re just getting started, that 95%-plus of the time, you’re going to want your data to be laid out more like this, in a more vertical structure.

So notice what we’ve done here. We’ve essentially transposed that data setup in the top-left corner from a horizontal structure to a more vertical structure in the bottom-right corner. I’ve now got a field name in each column header. This is called a tidy format.

And when you set up your data so that there’s a field in each column header, Tableau is allowed to do a couple of things from there. It’s first going to assume that each column header is a field name. So it’s automatically or very easily able to gather what fields you’re working with.

It’s then going to look at that second row, which is the first row containing data. And it’s going to determine the data type. And then it’s going to determine a couple of classifications that we’ll talk about in the next couple of videos here on this track.

But just pointing this out as you’re getting started, you’re going to want your data to be laid out in that format in the bottom-right corner to get a lot of the use from the rest of this Fundamentals learning path.

This is also such a common scenario that I’ve created this shortened URL on the screen there. I’ll also put this in the related content below the video. But if you navigate to playfairdata.com/pivot, that will show you how to pivot data, do an unpivot, as well as even a double pivot in Tableau Prep, so a couple of helpful ways to get that format from the top-left layout to that bottom-right layout.

By the way, I also intend to eventually record that here at Playfair Data TV. But if you need help in the short term, check out that shortened URL, get your data in a tidy format, and you can hit the ground running with the rest of the videos here.

This has been Ryan with Playfair Data TV – thanks for watching!