A Comprehensive Guide to Combining Data in Tableau
Have you ever wanted to create a dashboard in Tableau but you do not want to use just one data source? There might be instances where we want to combine several data sources to tell our story! Tableau does not restrict you to using just one data source, in fact, there are four different methods of combining data using Tableau. The method to use depends on the data and use case. In this article, we will be looking at the different methods and how we can apply them.
A. Creating Relationships between Tables
When combining data, relationships are usually considered as the first approach. Imagine we have details about a high school in an Excel workbook. The workbook contains information about Students, Instructors and Courses in different worksheets (tables). We can create a relationship between these three tables (as seen in the image below) using Tableau.
When creating relationships in Tableau, we see a line that connects the tables. If a common field name exists, Tableau automatically creates the connection based on this field. To create a relationship, simply drag a table to the canvas, then add the other tables to the canvas.
In cases where the field names are not the same, for instance, in this case, the Students’ table contains Project Supervisor instead of Instructor Name, you have to manually define the field names to show the relationship between the tables to Tableau. The video below provides a step-by-step approach to resolving this.
It is important to note that relationships do not merge these tables, each table still maintains its originality and level of detail.
B. Combining Data Using Joins
Another way to combine data using Tableau is by using Joins. Remember, when we talked about relationships, we stated that relationships do not merge tables. However, joins do! When we combine data using a join, we extend the table by adding more fields to the data.
Now, there are different types of joins depending on your use case:
- Inner Join: This is the default type of join, it only contains records that have matches in the two tables
- Left Join : This type of join contains all the records in the left table and matching records from the right table
- Right Join: This type of join contains all the records in the right table and matching records from the left table
- Full Outer Join: This contains all records from both tables, in cases where there are no matches, you’d see null values.
Using the same high school example, suppose we have information about 25 Students in a table called Student Information and the grades of 10 students in a table called Student Grades and we want to combine the data from this two tables using joins. For Inner joins, this will produce a table containing student information and grades with matching records from the two tables.
To create a join in tableau, simply connect to your data source and drag it to the canvas. Double-click this to open the physical layer and then drag the second table to the side. For a visual illustration, watch the video below.
What do you think would happen if we chose Left join? In this case, all the records from the student information table would be included with matching records from the student result table. For a full-outer join, all the records from both tables would be included. To change the join type, simply click on the venn diagram and select the join type you prefer.
One advantage of joins over relationships is that you can use multiple data sources in joins. By creating a relationship in Tableau, you can only connect multiple tables in one data source (e.g. an Excel workbook with multiple tables). However, for joins, you can merge tables from one or more data sources.
C. Combining Data Using Unions
Another way of combining data in Tableau is by using unions. When we talked about joins, you’d recall we said we could merge two datasets by fields using joins. The case is a little different for unions. In Tableau, unions append data by rows. What do I mean?
Using our high school case study, suppose we have student information data for three different classes: Class 1, Class 2 and Class 3 and we would like to combine it into one dataset containing information from the three classes, we can do this using unions.
When using unions, it is important to note that the datasets you are combining should all be of the same structure. It is good practice to ensure that the field names and data types are the same for all the datasets to avoid missing values. Also, to combine your data using unions, your data has to be from the same connection.
You can manually combine data by union using two basic methods. First, click on the “New Union” on the data source page and then select the tables you want to combine in the prompt. Alternatively, you can highlight all the tables you want to combine and drag them all to the canvas at once.
D. Creating Blends in Tableau
Blends is the last method of combining data that we will be discussing. A blend is different from the other methods because it combines data in a tableau worksheet instead. It establishes a linking field it uses to combine data from multiple data sources. In blends, one data source is known as the primary data source (has a green checkmark) while subsequent data sources are the secondary data source (has an orange checkmark). The specific use case of blends is to combine multiple data sources in a single visual.
When creating a visual using multiple data sources, the data whose field is first used is known as the primary data source. A blend works like a left join showing all the data from the primary data source and matching records from the secondary data source. It is important to note that the status of primary and secondary data sources is limited to a specific worksheet. You can change which data is primary or secondary in different worksheets in the same workbook.
To create a blend, connect to a data source and then create a worksheet. Go to the data menu and add a new data source. Your data sources must have a common field name for Tableau to create a blend. Next, select one or more fields from a data source — this becomes your primary data source. You can also add other fields from your secondary data source. Using our high school example, we will be creating a table with specific fields from School Information data and Student Grades data. As stated earlier, the status of the data source as primary or secondary can be changed in different worksheets. This video explains how to do this in Tableau.
Thank you for staying tuned! We have discussed the four different methods we can apply to combine data in Tableau and their different use cases. We started by introducing relationships, tableau’s default way of combining data by establishing relationships between tables, and then we discussed the various types of joins, after this, we discussed unions for combining data by records and finally blends. So, next time you want to create that visual and one data source is not sufficient, you know what to do😉.
I hope you learnt something from this article! Kindly clap and share with others. For more articles on data analytics, mathematics and statistics, follow me on Medium. Adios!
In Plain English
Thank you for being a part of our community! Before you go:
- Be sure to clap and follow the writer! 👏
- You can find even more content at PlainEnglish.io 🚀
- Sign up for our free weekly newsletter. 🗞️
- Follow us on Twitter, LinkedIn, YouTube, and Discord.
