Tableau Union

What is Tableau Union?

Tableau Union refers to a feature of combining two or more datasets or tables by adjoining data rows from one table to another to create a single dataset. When you union your data in Tableau, you add the data from one table to another table. For example, you may have a master table containing the historical sales data for a superstore, and now you want to append the sales data for the current month to the master table. In this case, you can use the Tableau Union feature. Tableau Union works when the underlying tables are similar, i.e., the same data type, field names, and number of fields.

A sample Tableau Union is provided below for reference.

Tableau Union - Sample
Key Takeaways
  1. Tableau Union combines two or more datasets or tables by adjoining data rows from one table to another to create a single dataset. 
  2. Tableau Union works only when the underlying tables are similar, i.e., they have the same data type, field names, and number of fields. Similarly, a union requires all the underlying tables to be from the same data connection.
  3. Tableau 2 different ways of creating a union of tables, worksheets, Excel workbook or Excel worksheet names, any text file names, .pdf file names, database table names, or Google Sheets workbook. These include
    1. Union tables manually.
    2. Union tables using wildcards
  4. With Tableau, you can create, rename, or remove unions. Additionally, you can add and remove tables in Union and merge mismatched fields.

How to create a Union in Tableau? 

Tableau allows 2 different ways of creating a union, which includes:

#1 – Union Tables Manually

In this method, you can manually union distinct tables in Tableau. To union tables manually, you can follow the steps outlined below:

Step 1: On the Data Source tab, import the datasets into the Tableau interface. 

Tableau Union - Method 1 - Step 1

Step 2: Double-click New Union to set up the Tableau union. It will open a Union dialog box, as shown below.

Tableau Union - Method 1 - Step 2

Step 3: Drag a table from the left pane to the Union dialog box.

Tableau Union - Method 1 - Step 3

Step 4: Select the remaining tables from the left pane and drag them one by one directly below the first table. Click on OK to save the changes.

Tableau Union - Method 1 - Step 4

Note: You can also select multiple tables at once using the Shift or Ctrl key and drag them directly below the first table to create a union. Tableau union 2 data sources won’t work if they are from different data connections.

#2 – Union Tables using Wildcards

In this method, you can set up a search criteria that will allow you to include the tables you want to union automatically. Wildcards involve using a character called asterisk (*) to instruct Tableau to match a pattern of characters or any sequence in an Excel workbook or Excel worksheet names, any text file names, .pdf file names, database table names, or Google Sheets workbook, etc.

When you use a wildcard character, the search is scoped to the selected data connection across the above-mentioned files. All the connections and tables available in a connection are listed on the left pane of the Data Source tab.

To create a Tableau union of tables using wildcards, follow the steps below:

Step 1: Import the dataset into Tableau Desktop using the Tableau interface.

Method 2 - Step 1

Step 2: Double-click New Union to set up the union. It will open a Union dialog box, as shown below.

Tableau Union - Method 2 - Step 2

Step 3: Click Wildcard (automatic) in the Union dialog box.

Tableau Union - Method 2 - Step 3

Step 4: Enter the search criteria (as shown below) that you want Tableau to use to find tables to include in the union.

For example, here, we have provided a matching pattern of *24 against the Include text box. Tableau will search for this pattern in all the Excel sheets in Tableau from the selected connection.

Click on OK to create a union of tables.

Tableau Union - Method 2 - Step 4

You will see Tableau has created a union of tables.

Tableau Union - Method 2 - Step 4 - tables

Examples

In this section, we will look at a few examples of the union operations in Tableau using different datasets for demonstration purposes.

Example #1 – Rename a Union

In this example, we will demonstrate how to rename a union in Tableau. We have used the Online_Sales_Data dataset, which contains sales details for different products across three different months, Jan’24, Feb’24, and Mar’24, along with financial details.

To rename a Tableau union, follow the instructions outlined below:

Step 1: Import the Online_Sales_Data dataset into Tableau. You will notice all the worksheets listed under Sheets in the left pane in the Data Source tab.

Example 1 - Step 1

Step 2: Now, use one of the methods detailed in the How to create a union in Tableau section to create a table union in Tableau. 

Once the Tableau union is created, you will notice that the underlying data now contains values from those union tables.

Example 1 - Step 2

Step 3: Right-click on the Union table on the physical layer canvas of the Data Source tab and choose the Rename option from the context menu.

Tableau Union - Example 1 - Step 3

Step 4: Enter a new name of the Tableau union you want to rename and hit Enter.

Tableau Union - Example 1 - Step 4

Tableau will rename the union, as shown below.

Tableau Union - Example 1 - Step 4 - rename

Example #2 – Add/Remove tables in Union

In this example, we will show how to add/remove tables in Union in Tableau. We will leverage the same data as example 1 to demonstrate this feature. Here, we have added more sheets to the Online Sales Data workbook.

To add/remove tables in Tableau, follow the instructions below:

Step 1: Use File Navigator to import the Online Sales Data dataset into Tableau using the Tableau Desktop interface.

Example 2 - Step 1

Step 2: Create a union of tables using either manual or wildcards in Tableau. Here, we have created a union using the Jan24, Feb24, and Mar24 worksheets, as shown below.

Tableau Union - Example 2 - Step 2

Step 3: Right-click on the Union table on the physical layer canvas of the Data Source tab and choose the Edit Union option from the context menu.

Tableau Union - Example 2 - Step 3

Step 4: Drag the table you want to add to the union and then click on OK to save the changes.

Example 2 - Step 4

Now you can see all 4 tables that are part of the union table, as shown below.

Tableau Union - Example 2 - Step 4 - union

Step 5: In the Union screen, hover your mouse over a table you want to remove from the union.

Here, we want to remove Feb24 from the union by clicking on the X button next to Feb24.

Tableau Union - Example 2 - Step 5

Now your union looks like the one below.

Tableau Union - Example 2 - Step 5 - Union

Example #3 – Remove a union

Now let’s look at an example of how you can remove a union in Tableau. To demo this, follow the steps outlined below:

Step 1: Right-click on the Union table on the physical layer canvas of the Data Source tab and choose the Remove option from the context menu.

Example 3 - Step 1

Once you click on the Remove option above, Tableau will remove the union, as shown below.

Tableau Union - Example 3 - Step 1 - Remove

Example #4 – Edit Unions in Tableau

In this example, we will look at how you can edit unions in Tableau. For this demo, we will 1st create a union using the dataset previously used in earlier examples and then edit it.

Follow the instructions outlined below to edit unions in Tableau:

Step 1: Create a Tableau union table using the steps mentioned in previous examples. The union table is visible in Tableau on Canvas.

Example 4 - Step 1

Step 2: Click on the down arrow next to the Union table and select the Edit Union option.

Tableau Union - Example 4 - Step 2

Step 3: Drag a table to the union table to add more tables. Alternatively, you can remove any existing tables. Click on OK to save the changes. 

Tableau Union - Example 4 - Step 3

Example #5 – Merge Mismatched Field in the Union

In this example, we will demonstrate how to merge mismatched fields in the Union in Tableau. For this demo, we have created sample sales data for May 24 in the Excel worksheet. The May 24 data contains the sales data for May 2024, similar to other worksheets we have used in previous examples. However, the alignment of data fields in this worksheet is different from that in the other worksheets.

Example 5 - Data

To merge mismatched fields in the union, follow the instructions below:

Step 1: Create a union in Tableau in the canvas. Here, we have created a union of four datasets, as shown below.

Tableau Union - Example 5 - Step 1

Step 2: Right-click on the Union table on the physical layer canvas of the Data Source tab and choose the Edit Union option from the context menu.

Example 5 - Step 2

Step 3: Add the May24 table to the union by dragging it to the Union window. Click on OK to save the changes.

Tableau Union - Example 5 - Step 3

Tableau will create a union table in the canvas. If you notice, the union table has null values in Product Category and Payment Method as the field names from May24 don’t match these nomenclature resulting in null values. May24 table has the field names as Product Cat and Payment, respectively.

Tableau Union - Example 5 - Step 3 - tables

Step 4: Select the field names you want to merge in the grid and then choose the Merge Mismatched Fields option from the context menu.

Tableau Union - Example 5 - Step 4

Tableau will merge these two fields into a single column, and you will now see the values populated in the merged column.

Tableau Union - Example 5 - Step 4 - merged column

Step 5: Repeat the same process to merge the mismatched fields. Here we have merged the Product Category and Product Cat fields as shown below.

Tableau Union - Example 5 - Step 5

As you can see, both the fields are now merged, and values are now populated instead of null.

Tableau Union - Example 5 - Step 5 - Values

Important Things to Note

  • The union doesn’t remove any duplicate data as it simply appends from one table to another.
  • A single union can have a maximum of ten data inputs to maximize the union performance in Tableau. 
  • If you require more than ten files or tables, it is recommended that you union the files in the input step.
  • Union requires the underlying tables to come from the same data connection to union data in the Tableau Data Source. Tableau union from different connections are not supported.
  • Union works only when the tables are similar in terms of data type, number of fields, and field names.
  • You can add multiple tables to a union at a time by pressing Shift or Ctrl in Windows systems, choosing the tables to be unioned in the left pane, and then dragging them directly below the 1st table.

Frequently Asked Questions (FAQs)

1. Is it possible to UNION tables with different structures?

Yes, you can union tables with different structures. It depends on how you want to union it. For example, if you are joining two tables with the same number of fields but different similar column names, i.e., say Product column in Table A vs. Product Category in Table B, then you can use the Merge Mismatched Field option to combine these two fields. Otherwise, the union table will produce extra fields with values populated from their appropriate context and for the remaining ones, it will be populated as NULL.

FAQ 1

2. How do I handle data type mismatches in a UNION in Tableau?

To handle data type mismatches in a UNION in Tableau, you can use Tableau Prep to change the data type of one of the fields to ensure they are compatible with each other. Any changes applied in Tableau Prep can be identified by noting the change data type icon at the top of the merged field.



However, in cases where the correct data type is not picked by Tableau Prep, it is recommended to undo the merge and choose Separate Inputs with Different Types.

FAQ 2 - separate inputs

You can then merge the fields again by first changing the data type of one of the fields and then using the suggestions in the Additional merge field options.

3. Can I UNION data within the same data source in Tableau?

Yes, Tableau supports union data within the same data source. You can use a manual or wildcard character approach to union the tables available in the data connection. To understand this in detail, refer to the How to Create a Union in the Tableau section.

4. Can I use UNION with extracts in Tableau?

Yes, you can use Union with extracts in Tableau. You can union with extracts by following one of the options, i.e., manually creating union tables or using wildcards. However, ensure that all those extracts are from the same data connection and should have similar structures. If there are mismatches in the structure, such as data type and field names, then you have to either change the data type to align with other fields or merge the mismatched fields in the union.

Guide to What is the Tableau Union. We explain 2 different methods to create a union in tableau with examples, points to note, and downloadable template. You can learn more from the following articles –

Tableau Count Distinct

Tableau Group By

Tableau Gauge Chart

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *