Tutorial: Combining Data in Tableau Public

🎯 Learning Goals

Learn how to blend data from different data sources

Understand the difference between joining and blending data in Tableau

Learn how to create inner, left, right, and full outer joins

Apply data joining and blending techniques to create insightful visualizations

📗 Technical Vocabulary

Data Joining

Inner Join

Left Join

Right Join

Full Outer Join

Data Blending

Primary Data Source

Secondary Data Source

Data Relationship

👋

Before we dive into Tableau, let's consider why we need to combine data. Often, the data we need for analysis is not in a single table. For example, we might have one table with sales data and another with customer information. To analyze sales by customer region, we would need to combine these two tables.

What potential problems could arise when combining data?

What are some other scenarios where you might need to combine data from different tables or sources?

✨ Relationships vs Joins: What’s the difference?

Tableau offers two different methods to combine data from multiple tables: relationships and joins. Both of these have their own pros and cons, so we’ll start with exploring and gaining familiarity with the terms!

Relationships are commonly used in most cases, since they excel at preserving the level of detail in the original data table. Data is combined only when needed, which leads to fewer duplicated rows and better performance. Adaptability is the main highlight here!

As for joins, these are more direct combinations of tables using a physical join (we’ll be learning about inner, left, right, and full outer joins later!) to merge the tables immediately. A key difference here is that the joins are fixed, so they do not adjust dynamically like in relationships. There is less flexibility, although there is more control over how rows are matched, especially if you need specific filtering or row duplication.

💡

Key Takeaways!

Relationships are dynamic and allow Tableau to decide how to combine data when it’s needed.

Joins are fixed merges that lock data together right from the get-go, often creating duplicates.

✨ Let’s create a Join!

Before we get started, there are a few common issues to be aware of in order to save a lot of time from troubleshooting:

⚠️

In order to create or edit a join, you have to first open a logical table in the relationship canvas (same view where you define how tables are related) and access the join canvas.

When joining two tables together, they must be the same data type (e.g., Age, Number of Followers are both integers or both string values). The join will break if you change the data type after making the join!

If a field is deleted that was used for join logic, Tableau will break the join since that field was needed to link tables.

Now, let’s begin!

First, start by loading in the data source as you normally would (For a refresher, you can find this in the Data Cleaning With Tableau lesson!).

It’s important to note that Tableau can join tables that come from either two tables in the same database or two sheets in the same Excel file. When combining data from two different sources, this is known as a cross-database join (e.g. a SQL database mixed with an Excel sheet file — jump down to this block here). Tableau distinguishes the data origins through distinct color-coding.

From the Data Source view, take the first table from the Sheets column (underneath the Connections column on the right-hand side) and drag onto the canvas area.

Note that this is the relationship canvas, but we will want to switch to working in the join canvas!

To enter the join canvas, start by either selecting the drop-down toggle on the first table and clicking “Open” or double-click.

You can now add in another table by once again dragging a table from the Sheets column. A join icon of several circles appears and “links” the two together, showing a menu of different options (join operators). Select one along with a field from one of the tables used in the data source.

You might be wondering what these different join operators mean, luckily we’re here to answer that burning question! As mentioned before, there are four different types of joins that Tableau offers: inner, left, right, and full outer.

💡

Pro-tip!

A helpful way to understand join types is by thinking of them as Venn diagrams. Each type determines which values to include or exclude based on how the tables overlap. When choosing a join, focus on which data you want to keep from each table based on matching values!

Inner:

Result is a table that contains values with matches from both tables. If a value does not match across either table, it gets dropped.

Left:

Result is a table that contains values from the left table, and any corresponding matches from the right table. In the case that the left table doesn’t have a match from the right, this value will be displayed as Null.

Right:

Result is a table that contains values from the right table, and any corresponding matches from the left table. In the case that the right table doesn’t have a match from the left, this value will be displayed as Null.

Full Outer:

Result is a table that contains every value from both tables. In the case that a value from one table doesn’t match the other, a Null value will be in its place.

🚨

Note: Not every database can support all join operations. If one is unavailable, this just indicates that there is a constraint in your data source.

✨ All about Join Clauses!

When we create joins, we need to tell Tableau which fields are shared between the tables, along with how to align the matching rows. This is what a join clause is used for, most often with the (=) operator (> and ≠ are also possible and referred to as non-equi joins).

Tableau makes this even more powerful by allowing multiple join clauses. For example, if you had First Name and Last Name stored in separate columns, you could join by using:


First Name = First Name and Last Name = Last Name

-- If the last name was shared between tables...
--but the first was not, you can use the <> (not equal operator)

First Name <> First Name and Last Name = Last Name

To make this even more leveled-up, join clauses can also be calculations! An example below is the concatenation of First Name and Last Name .


[First Name] + [Last Name] = [First Name] + [Last Name]

🚨

Note: In the case of Null values, most often Tableau will return data without the rows that contain them. However, there is an option to join fields with Null values along with other fields that have Null values.

On the Data Source view, at the top of the screen select Data and then in the drop-down choose Join null values to null values.

In the case that this option isn’t available, it is due to the constraints of your data source.

✨ Cross-database Joining

Earlier, we had mentioned that Tableau offers the functionality to join tables from across different data sources (how cool!). In order to do this, a new connection has to be made to each individual database before joining.

After the first data source has been loaded, in the Connections column there is a small plus button that can be used to make another connection.

It’s important to consider that just like how not all join operations are supported by each data source, the same goes for cross-database joining.

When moving forward with analyzing data, such as when creating a new sheet, this connection then becomes a combined data source!

💫

And with that, we’ve reached the end of this tutorial! Hopefully we will be able to join you on your next cross-database adventure be-clause we can’t wait to see what you’ll create!