Appending tables in Tableau is a fundamental skill for business users to consolidate and analyze data more effectively. Tableau utilizes the union feature, a term most commonly used in SQL that is used to combine multiple files into one continuous dataset. We’ll explain how to setup a union and some best practices around setting up your data to avoid errors during the process.
Appending tables, also known as data union, in Tableau involves stacking data from similar tables into one comprehensive dataset. This technique is often applied when working with time-series data or datasets that share a common structure but either cover different categories of products or different time periods.
Let’s jump in!
Table of Contents
When to Append Table in Tableau
Our first recommendation when working with source data in Tableau is to connect to a live data source, such as a SQL database whenever possible. SQL tables will typically contain more information than individual files or reports and avoid the need for additional data transformation. Unfortunately, it’s not always possible or practical as it often requires assistance from IT and the data may not be easy to parse out.
Some of the scenarios that commonly require the need to append or union data together in Tableau include:
- Consolidating monthly or yearly data reports.
- Merging similar datasets from different sources.
- Analyzing data that is structured identically but stored separately.
Now let’s take a look at how to append multiple datasets.
How to Append Tables in Tableau
To append tables in table, create a new Union in the Data Source tab. Tableau gives developers the ability to either drag and drop multiple tables from a data source into the data transformation canvas, or they can use the New Union settings for more granular control. While the drag and drop method is fast, the New Union editor provides more advanced options.
Here’s how to Append multiple tables using the New Union option in Tableau.
Step 1. Import a data source
To import data into Tableau, either go to Data, New Source or Add a connection from the Data Source screen.
In our example, we imported a single Microsoft Excel file that has multiple tabs. Each tab in the Excel workbook relates to a different month of sales. When you import Excel files with multiple sheets, Tableau will present it as a single connection to a file, and then list each individual sheet below it.
To view connections and sheets, make sure that you’ve selected the Data Source view at the bottom of Tableau. This is the screen that lets you modify and edit the data model. While other tabs let you build visualizations and add them to dashboards.
Step 2. Create a Union to Append Multiple Tables Together
From the data sources tab, there are a few different options to be aware of for appending Tableau tables together. We’ll look at each one.
Option 1 – Append Tables with the “New Union” Button
The first way to append different tables together in Tableau is to double click the New Union button on the left side of the Data Source page. This will launch the Union Editor which allows you to drag and drop sheets or tables into the Union editor. When you click OK it will save the changes and append all datasets by automatically matching up the column names.
Option 2 – Drag and Drop Tables into the Canvas
Sometimes a faster way to Union tables together in Tableau is to drag and drop a table on top of another that’s already on the data transformation canvas. In the screenshot below we clicked on the September table and dragged it on top of the July table.
You will see a Union highlight show up, and if you drop the file on the Union highlight it will automatically append the data together.
Even though this method is fast, one of the reasons that we typically avoid it is that when you drag and drop them you lose visibility to which ones you’ve already added which can lead to some unintended consequences. Tableau will not prompt you if a table has already been added, meaning that you could add the same table multiple times which will throw off your data.
If you use the drag and drop method, we recommend editing the union to launch the new union editor to make sure that the tables you’ve added have not been duplicated.
How to Edit Unions in Tableau
Once you append and combine several datasets in Tableau, you can click on the small triangle that appears when hovering your mouse over it in the data source tab, then select Edit Union. This launches the Union editor and you can view all of the tables that are being consolidated and adjust other settings as needed.
The settings here will be the same as if you were to double click New Union to append tables.
Use Multi-Select for Faster Unions
To combine tables even faster, you can use standard windows Multi-Select functions to select multiple tables to either drag and drop into the canvas or the Union Multiple Tables screen from the Union editor.
SHIFT + Left Click to select a list of files from beginning to end
CTRL + Left Click to select multiple files that are not in sequential order
The example below shows using a multi-select while holding Control on a windows computer to select the August and September files that can now be dragged and dropped at the same time into the Union Multiple Tables window.
Note: If you do choose to hold SHIFT to select all of the items in a list, make sure that you don’t select a table that’s already been added into the dataset to avoid duplication.
Appending Multiple Files from the Same Folder
If you want to consolidate multiple files that are saved in the same folder, or organized into a series of sub-folders, check out our comprehensive guide to consolidating multiple files into Tableau. While it’s not as common as performing a simple union it’s an extremely useful feature to have in your Tableau toolbox. The technique can also be coupled with common RPA solutions to automate the saving of scheduled reports from various systems that don’t support direct connections to make your reports more automated.
Union Tables Across Data Connections
Tableau Desktop does not support the ability to append or union tables from different data sources. Common workarounds include using a SQL Union as part of an incoming SQL query or utilize alternative data prep software such as Tableau Prep or Alteryx.
Our example from earlier utilized multiple sheets in the same data source. If you are working with multiple data sources it will look slightly different, such as the screenshot below where each file is a different connection.
The video below from Tableau offers more guidance on performing unions either directly within Tableau or utilizing Tableau Prep.
If you have access to Tableau Prep it’s our recommended solution for importing data. The lack of an ability to union data across data sources in Tableau Desktop has existed for years and it’s unlikely to be added at this point if it hasn’t been added already.
Appending Tables with Mismatched Columns in Tableau
As a best practice, we recommend using the union feature to append data when the columns match. Tableau will auto-match columns based on column titles and the more consistent data is coming into the union the less data transformation will need to be done.
If tables are appended that have a different number of columns, Tableau will match up as many as it can and then represent the additional columns will null values when no data is present for them.
Columns are auto-matched by name, but you can also override them and manually assign them by creating a Union relationship that is equivalent to setting something like Column A = Column B and Tableau will try to match them. If you do this, just be sure that data types are the same across columns to avoid potential errors.
Conclusion
Appending tables in Tableau is extremely fast using the drag and drop method, especially when combined with multi-select. Just be careful when doing so to make sure that you don’t add the same table multiple times. Tableau will not give you an error that a table has been added more than once, it will re-name the table and duplicate the data which can lead to incorrect visualizations.
Tableau Desktop does not support the ability to union multiple tables across data sources. It’s a bizarre omission in functionality that people have been working around for years and is unlikely to be added in the near future. In these circumstances, we recommend using Tableau Prep or a SQL Union.