One of the most popular Business Intelligence tools available in the market is Tableau. It allows users to create and share dashboards in a secure environment in which decision-makers can view, search, or reference data in their workflows. It also allows users to integrate data from multiple sources and perform a common analysis on it. This article will provide you with an understanding of how you can easily work with Union in Tableau to combine data from multiple sources.
Introduction to Tableau
Tableau is a well-known Business Intelligence and Data Analytics tool created to help organizational decision-makers visualize and understand data and make data-driven decisions. It can be used to create a wide range of interactive visualizations to present the data and derive useful insights from it.
Understanding the Union Operation
A Union operation is primarily used to combine the contents of two or more tables. In order to perform a Union operation, the number of columns and data types of the data stored in those columns must be the same across all tables on which the Union operation is being performed. The Union operation ensures that all duplicates are removed from the resultant table. This means that if there is a record that is present across different tables, it would only be present once in the resultant table.
For example, suppose there are three tables that represent the Sales of a store across three different months. The Sales for those three months is as follows:
Now, if an analysis is to be done to measure the performance of that store, all these tables would have to be combined to make a single table, and only then can a useful analysis be performed on it. The combined table that would be formed after the Union operation is performed across all three tables is as follows:
Performing Union in Tableau
Users have the ability to seamlessly perform Union in Tableau as long as the conditions on the number of columns and data type of the columns are met. Also, the data sources of all the tables should be the same in order to successfully perform the Union. For example, users can perform Union in Tableau for two tables that are coming from Microsoft Excel or two tables coming from SQL but cannot perform the operation if one table is coming from Microsoft Excel and the other from SQL.
Once all the required data has been imported into Tableau, users will be able to see a section called “New Union” in the left pane of the Data Source page if the data sources support Union in Tableau.
If the “New Union” indicator is visible, then that means that users can perform the necessary Union in Tableau using one of the following methods:
Manual Union in Tableau
This process involves manually selecting each table on which the Union operation has to be performed. It can be set up by implementing the following steps:
- In the left pane of your Data Source page, double click on the “New Union” indicator.
- Drag and drop a table from the left pane to the New Union dialog box.
- Add one or more tables that you wish to add to the Union operation from the left pane. You can also select multiple tables using the Shift button and drag-and-drop all of them to the New Union dialog box.
- Click on “OK” to complete the Manual Union in Tableau operation.
Automatic Union in Tableau
If there are a large number of sources that you wish to perform the Union operation on, selecting each source manually would require a lot of time and effort. Hence, the feature of Automatic Union in Tableau using Wildcards was introduced.
For example, suppose you wish to perform an analysis of all your Sales data from 2016 and your data source names are in the format “MONTH_2016.” The first step would be to perform a Union operation on all the tables which can be done by implementing the following steps:
- In the left pane of your Data Source page, double click on the “New Union” indicator.
- Select the “Wildcard (automatic)” option found in the upper right corner of the New Union dialog box.
- Enter the search criteria based on which you wish to include your data sources. For the example stated above, the criteria would be “*2016”. The “*” signifies more than one character in the Data Source name. This means that “*2016” signifies any data source with a name that ends with “2016.”
- Click on “OK” to complete the Automatic Union in Tableau operation.
Editing Unions in Tableau
Tableau houses a functionality that allows users to perform the following edit operations on Unions:
1) Renaming a Union in Tableau
Users can rename a Union by implementing the following steps:
- Double click on the logical table that contains the unioned data.
- Double click on the Union table on the Physical Layer Canvas.
- Enter a new name for the Union table.
2) Modifying a Union in Tableau
Users can modify a Union to add or remove tables from it by implementing the following steps:
- Double click on the logical table that contains the unioned data.
- Click on the Union drop-down arrow and select “Edit Union.”
- You will see a list of tables that are included in the Union. If you wish to remove a table, you can hover over the table name and click the remove icon corresponding to it. Similarly, if you wish to add a table, you can drag and drop a table from the left pane on the Data Source page.
- Click on “OK” to finish editing the Union in Tableau.
3) Removing a Union in Tableau
You can remove a Union by implementing the following steps:
- Double click on the logical table that contains the unioned data.
- Click on the Union drop-down arrow and select “Remove.”
Read more about : Tableau Open Source
Merging Mismatched Field in the Union
When column names of the tables on which the Union operation being performed do not match, it results in the addition of NULL values in the resultant table.
For example, consider the previous sample of Sales data of a store for three months.
Suppose you wish to Union these tables along with the Sales data from August which is as follows:
It can be observed that the number of columns and the data type of column is the same and hence, the Union operation will be performed. However, the customer name in the first three tables is stored in the “CUSTOMER” column but stored in the “CUST” column in the August table. As a result, the Union operation for these four tables would result in the creation of the following table:
In order to get the desired results, the user would have to merge the “CUSTOMER” and the “CUST” columns.
This operation can be performed by implementing the following steps:
- Select two or more columns that you wish to merge.
- Click on the column drop-down arrow and select “Merge Mismatched Fields.”
Conclusion
This article provided you with an in-depth understanding of how you can perform Union in Tableau, make any necessary changes you wish to in that Union and also resolve any mismatch errors that might occur while performing the Union operation.
One of the most important factors that you have to take into consideration while choosing a Data Analysis and Business Intelligence tool for your business is whether the tool has support for your data sources or not. Most modern businesses make use of multiple platforms to run their day-to-day operations. Hence, finding a tool that provides support for all your platforms is a hard nut to crack. As a result, businesses have to rely on Data Integration platforms. Building an in-house Data Integration solution would require immense engineering bandwidth and resources. Hence, you should consider using automated Data Integration platforms like Hevo.
Hevo helps you directly transfer data from a source of your choice to a Data Warehouse, Business Intelligence tools such as Tableau, etc., or desired destination in a fully automated and secure manner without having to write the code. It will make your life easier and make data migration hassle-free. It is User-Friendly, Reliable, and Secure.
FAQ on Working with Union in Tableau
How does union work in Tableau?
The union operation in Tableau combines rows from two or more tables, appending them to create a single dataset.
What is the difference between union and join in Tableau prep?
Union: Appends rows from two or more tables into a single dataset. Requires tables to have the same structure (similar columns).
Join: Merges tables based on common fields, combining columns side by side and can join tables with different structures, using key fields to match rows.
Can we union two different data sources in Tableau?
You can union tables from different data sources by creating a multi-connection data source in Tableau.
Details on Hevo pricing can be found here. Give Hevo a try by sign up for the 14-day free trial today.
Manik is a passionate data enthusiast with extensive experience in data engineering and infrastructure. He excels in writing highly technical content, drawing from his background in data science and big data. Manik's problem-solving skills and analytical thinking drive him to create impactful content for data professionals, helping them navigate their day-to-day challenges. He holds a Bachelor's degree in Computers and Communication, with a minor in Big Data, from Manipal Institute of Technology.