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.
- Let us create a new sheet and rename it as
Union. - Click Ctrl+D to connect to the data and select the Excel option and then select the
Union data.xlsxfile fromDocumentsMy Tableau RepositoryDatasourcesTableau Cookbook data. Click on Open and we will get to see the data connection window. Refer to the following screenshot:
- As we see in the preceding screenshot, once we establish a connection to the
Union data.xlsxfile, 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:
- Next, let us drag Q1 2015 into the new box which says Union. Refer to the following screenshot:
- We'll do the same for the rest of the tables and then our view will update as shown in the following screenshot:
- Now, click OK. Our view will be updated as shown in the following screenshot:
- Click on the Union sheet tab to view the Dimensions and Measures of this Union operation.
- Right-click and drag the Date field from the Dimensions pane and drop it into the Columns shelf. Select the DAY(Date) option as shown in the following screenshot:
- Next, let us drag Sales and drop it into the Rows shelf followed by dragging the Quarter field and dropping it into the Color shelf. Refer to 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.