Chapter 4. Connect to Data and Simple Transformations

The first thing you usually do when opening Tableau is to connect to a dataset. You already had a glimpse of data connection in the previous chapter when we used an Excel file. In this chapter, you'll see the most important options that Tableau offers when connecting to data. Here are our main focuses:

• Data connection
• Join data easily
• Union your data
• Simple transformation

To harmonize all the different words among the various data providers, we'll use the following terms through the chapter:

• Dataset to represent any sources. It is where your data is located. It could be an Excel file, a database on a server, or a file on the cloud. The dataset is what you want to connect to Tableau.

• Tables to represent, no matter the connection, a sheet in Excel or a table in a database. For example, there are three tables in the following screenshot:

• Data Source to represent the result of your connection, after all the transformations.

Let's now start with some...

Data connection

In this section, we'll see how to connect to different datasets (file or database). Of course, with more than 50 different connectors available, it's impossible to see each in detail. You can see, in the following screenshot, all the current available connections:

The goal when connecting to a dataset in Tableau is always the same:

1. Choose your connection (File/Server).
2. Connect by selecting the file or entering the login information.

3. Reach the Data Source workplace and have at least one table in the framed area: 

Let's start by seeing how to connect to files, then to databases, and, in the end, Spatial files.

Connecting to a file

In this section, you'll see two examples of file connections: an Excel file and then a CSV file.

Excel files

If you want to connect to an Excel file, follow these steps:

1. Click on Microsoft Excel in the list of available connections when you open Tableau.
2. Search for your file in your computer, double-click on it, and Tableau automatically opens the file.

3. The different...

Join data easily

A Join creates a single Data Source with columns coming from two (or more) tables. How do Joins work? How can you create them? Are there risks? You'll learn everything in this section.

The Join principle

So far, we've only used one table. A Join is automatically created when using two or more tables. There is always a left table and a right table. In the following screenshot, you see a Join between Orders (the left table), and People (the right table):

The result of a Join is a Data Source that contains the columns from the different tables. As highlighted in the screenshot, you can see that the Profit column comes from the Orders table, and the Person column comes from the People table:

There are four different types of join. Discover them in the next section.

Join requirements and types

To create a Join between two tables, you need at least one column in each table that contains the same values. These common columns create the link between the two tables. When you create a Join...

Union your data

If a Join adds columns, a Union adds lines.

Unions are useful when you have two (or more) tables with an identical structure that you want to combine to create a unique Data Source.

The typical use case is when you receive an Excel file with one sheet per year, and you need to compare those years. To do that, you need to combine those different sheets into a single Data Source. You can, of course, spend some time copying and pasting the data into a new, big Excel file, but with Tableau and Unions, you can combine them in a few clicks.

Note

To create useful Unions, the different tables must contain the same column names. Otherwise, Tableau will not consider them identical and will create new columns.

Let's start with an example.

A Union example

For this example, I created an Excel file with two sheets to Union.

Note

If you want to reproduce the example, you can download the Union example file from my website, book.ladataviz.com, in the section Chapter 4: Connect To Data and Simple...

Simple transformations

Tableau is not a data-preparation tool. It's always better to have a clean file to start with. However, Tableau offers some simple transformation tools. When you connect to a dataset, you can, for example, use the Data Interpreter, split a column into multiple columns, or pivot your data. Let's see a case for these transformations.

For this section, I created a dataset to clean in Excel, as illustrated here:

Note

If you want to reproduce the example, you can download the Dataset to clean  file from my website, book.ladataviz.com section in the Chapter 4: Connect To Data and Simple Transformation section, or use this direct link: https://ladataviz.com/wp-content/uploads/2018/09/Dataset-to-clean.xlsx.

Three things are problematic with this dataset:

• There is a column header for the years
• The countries and the cities are in the same column
• Each year is in a different column, preventing the creation of a simple line chart

Now, let's fix the first problem of this dataset with the...

Summary

We have finished our first chapter about data connections. We saw how to connect to different sorts of datasets and use some features, such as Join, Union, and some data transformation. Later in the book, you'll learn other useful and powerful data transformation features. 

After connecting to the data, you have a Data Source. You can directly use it to create visualizations, but I advise you to spend some time customizing and preparing your Data Source. There are many ways to enhance it and create something shareable, easy to understand, and compelling for your analysis.

In the next chapter, we'll learn how to build an efficient Data Source.