So far we have understood how Joins and Data Blending work. However, when we use Joins or blending, we end up appending columns from one table to another. Now imagine a situation where we need to append rows from one table to another. So, for example, imagine having separate tables for each quarter; each table contains the same information but only for the relevant quarter. Now if we wish to look at the performance of the entire year, then we will need the data from all these separate quarter tables. In this case, we will use the Union functionality. Let's see how we can do Unions in Tableau in the following recipe.
For this recipe, we will download the Excel file named Union data.xlsx
that has been uploaded on the following link:
https://1drv.ms/f/s!Av5QCoyLTBpnhkx2T7tGFlMQ32MR
We will download this file and save it to the DocumentsMy Tableau RepositoryDatasourcesTableau Cookbook data
folder. We will continue using the same workbook, My first Tableau Workbook
. Let us get started with the recipe.
Union
.Union data.xlsx
file from DocumentsMy Tableau RepositoryDatasourcesTableau Cookbook data
. Click on Open and we will get to see the data connection window. Refer to the following screenshot:Union data.xlsx
file, we will see that it contains four tables or worksheets named Q1 2015, Q2 2015, Q3 2015, and Q4 2015. Now we would like the data from all these tables or worksheets to be appended one below the other in one view so we can take a look at the total yearly values. In order to do so, let's start by double-clicking on the New Union option as shown in the following screenshot:As we saw in the earlier recipe, when we do a Union of all the four tables or worksheets, we get a single datasource with all the data appended row-wise. Further, if we take a look at the data file, the first three quarters had only the Sales and Profit fields, whereas the fourth quarter has a new measure called Quantity. However, when we are doing a Union, we get a new column for Quantity and this column shows null for the first three quarters and data only for the fourth quarter.
Further, there could be a situation where the field names across the tables do not have the same column header names. For example, one table could have a field called Product and the other could have a field called Product Name; however, these two fields are essentially the same. When doing a Union, this will give us two separate fields. To avoid this, we can use the Merge mismatched fields option. Refer to the following screenshot:
Further, when we do a Union across tables, there is some metadata information such as Sheet
and Table name
that is created in addition to the actual data. These fields help us get information about where our values are coming from.