Working with Union in Tableau Simplified

With most businesses relying on data-driven decision-making for effective cross-vertical operations, the use of various Business Intelligence and Data Analysis tools has increased exponentially. Data-driven decision-making can be defined as the process of using metrics, facts, and data to guide all strategic and business decisions that align with business objectives, goals, and initiatives. 

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.

Table of Contents

• Introduction to Tableau
• Understanding the Key Features of Tableau
• Understanding the Union Operation
• Performing Union in Tableau
  • Manual Union in Tableau
  • Automatic Union in Tableau
• Editing Unions in Tableau
  • Renaming a Union in Tableau
  • Modifying a Union in Tableau
  • Removing a Union in Tableau
• Merging Mismatched Field in the Union
• Conclusion

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.

Tableau was founded with a simple mission i.e. to put the power of data into the hands of businesses, allowing them to engage with their data, solve business problems and build value. Businesses of all sizes operating across different scales, rely on Business Intelligence tools like Tableau for an analysis of their data. Large-scale Enterprises like Unilever, which sells more than 400 different kinds of products in 190+ countries, to small-scale local businesses like Marination, an Asian-Hawaiian food truck eatery in Seattle make use of Tableau visualizations to analyze their business data seamlessly which would have been extremely difficult to do using tools like Microsoft Excel.

Tableau offers various products and services such as Tableau Desktop, Tableau Server, Tableau Online, and Tableau Reader along with its free version called Tableau Public as part of its suite.

More information on Tableau can be found here.

Understanding the Key Features of Tableau

Some of the key features of Tableau are as follows:

• Data Sources: Tableau offers seamless integration with a large number of widely used platforms for data storage such as File Systems like Microsoft Excel, CSV, etc., Relational Databases like Microsoft SQL Server, Oracle, IBM DB2, etc., Cloud-based Systems such as Google BigQuery, Windows Azure, etc., or other sources such as ODBC/JDBC.
• Attractive Visualizations: Tableau gives users the ability to create different types of data visualizations. For example, users can easily create the simplest visualizations such as a Pie Chart or Bar Chart or some of the most complex visualizations such as Bullet Chart, Gantt Chart, Boxplot, etc. Tableau also comes with information on geographical data such as Countries, Cities, Postal Codes, etc. that allows users to build visualizations using informative maps.
• Wildcard Union and Cross-Data Join: Users have the ability to perform Cross-Data Join and Union in Tableau. This helps in removing the barriers and time-consuming workarounds that were required due to the massive use of numerous data sources.
• Cross-Data Source Filtering: For data with common dimensions, Tableau gives users the ability to perform the filter operation across multiple data sources at the same time. This also results in all necessary edits or changes being made to all worksheets with the same data sources using the same set of filters automatically.
• Mobile Dashboards: Tableau allows users to format all their Reports and Dashboards in such a manner that it is compatible with mobile devices as well. It lets users create custom mobile layouts for their Reports or Dashboard specific to their device.

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
• Automatic Union in Tableau

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:

• Renaming a Union in Tableau
• Modifying a Union in Tableau
• Removing a Union in Tableau

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.”

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.

Details on Hevo pricing can be found here. Give Hevo a try by sign up for the 14-day free trial today.