Learn how to import and create data connections in Tableau for use in worksheets.
- [Instructor] So preparing our data set for Tableau is a necessity to make sure we can do the type of analysis we need to better explore and understand our data. So for this course, we're going to try and identify where online training will continue to grow around the world. To accomplish this goal, we are going to use the World Bank Development indicators data set, which I downloaded from databank.worldbank.org and I also pulled data from the UN data store for education spending as a percentage of GDP. So we've included extracts demoed in this course, in the exercise files.
Now let's jump over and take a look at the exported data. So the first one we are going to look at here is the World Bank Development indicators set and again, because we are trying to predict some information about the future, we really want to take a look at the trends. And so, looking at this data set, now this may not come up all the time, but it does come up a lot, where you may receive an Excel file or other data source from someone who used it to maintain data in the past and when working with Tableau, we really want our data organized in columns.
So Tableau is, works with the data in columns and the thing to notice from this data source is that our years now are not sorted by a single column, but instead, each year represents different columns of, sorting column E and working all the way over to column N. And so, having data across all of these columns would make it almost impossible to trend our data because we need our years or we need our year value to exist in a single column and then, have the data assigned to it in the column next to it.
So, one of the things we are going to have to do is rework this data to make it usable in Tableau. So to give you an example about how to do this or how to rework the data for bringing it in to Tableau. On the definitions and source page here, these are some of the indicators that I'm going to want to pull through to take a look at in Tableau and to rework the data, I'm just going to grab one of the indicators here, jump over to our data tab and now we're going to filter the series name and so you can see, this is the data we're going to need to move over.
Now it's fine for us to copy, kind of all these right over and I'm just going to go down to this sheet one and then paste these in and so now I've got my country, country code, series name, series code. Now what I'm going to want to rework is create a new year column and then, I'm going to create a power consumption column to just deal with the values coming in from this code. So now, if I go back to my data, I can grab my years across the top, hit control c and copy them, go back to my sheet.
Now I'm going to right-click and do paste special and I want to transpose. Now I'm going to back and grab my data, copy it again. Go back to my sheet, right-click, transpose. Now a couple of pieces, we want to make sure our year is numeral. That will help it trend correctly and display correctly, so I'm going to ahead and type in 2005, 2006, and now that I can create a trend in Excel, I'm going to highlight both of these and in the lower right hand corner, drag down and you'll see it, it's going to extend my range.
And because I want my variables to also be consistent, this would show up as a text in Tableau so we want to delete these, we're going to want to clear these out because we really want these to be effectively blank. They don't have data for this and now, I can copy the rest of this down so now I've reworked my data so that it is in an optimal format for Tableau. Now, I've gone ahead and done all of the revision already for these variables in this data revised tab, so all the variables we're going to be working with is in this last sheet, but wanted to show you kind of how we got to this data revised section.
So if we take a look at our UN data source, so the second data source that we're going to be using as a percentage of GDP, we can see this is actually organized the way we want to because we've got our countries over in column A, we've got our year in column B and we've got observation value or the percentage in column F, so this data source is fine and will work well in Tableau because everything is in single columns and so, it'll be great to work with. So one of our first steps in creating effective Tableau dashboards is making sure our data set is organized effectively for Tableau.
- Sketching your designs
- Formatting data
- Creating cross-data joins
- Creating visuals
- Grouping data
- Using stories vs. tabs
- Changing chart measures and types with interactive parameters
- Making iterations and improvements on your Tableau dashboard design