How to Union Two Data Sources in Tableau

Bringing together data from different sources is a daily reality for anyone working in analytics, and knowing how to stack data sources on top of each other is a fundamental skill. If you have monthly sales reports in separate files or regional data in different spreadsheet tabs, a union is exactly what you need. This article walks you through how to union data sources in Tableau, including the requirements, potential pitfalls, and step-by-step instructions.

What Exactly is a Union in Tableau?

In the simplest terms, a union in Tableau is the process of appending rows of data from one table to another. Imagine you have two identical stacks of paper, each with a list of contacts. A union is like taking one stack and placing it directly underneath the other to create one tall stack. You're not combining the information within each row, you're just adding more rows to your dataset.

For a union to work correctly, the tables you're combining should have the same structure. This means they should have the same number of columns, and those columns should contain the same type of information and ideally have the same name.

It's important not to confuse a union with a join. While they both combine data, they do it in completely different ways:

• Union: Appends rows (stacking data vertically). Your number of rows increases, while the number of columns stays the same.
• Join: Appends columns based on a common field (stitching data horizontally). Your number of columns increases, while the number of rows often stays the same (depending on the join type).

If you're merging monthly sales reports to create an annual one, you use a union. If you're adding customer shipping details to an order table using a CustomerID field, you use a join.

When Should You Use a Union?

Unions are perfect for consolidating data that has been split apart for organizational or logistical reasons. Here are a few common scenarios where a union is the best approach:

• Combining Time-Based Data: This is the most classic use case. You might have sales data exported into separate CSV files for each month (e.g., Sales-Jan-2024.csv, Sales-Feb-2024.csv, etc.). A union lets you stack them all into a single, continuous dataset for year-to-date analysis.
• Consolidating Regional or Departmental Data: Imagine an Excel workbook where sales reports for different regions (North America, Europe, Asia) are kept on separate sheets. As long as each sheet has the same column layout, you can union them to get a global view of your sales performance.
• Aggregating Data from Identical Systems: If you're managing multiple Shopify stores that all use the same report structure, you could export order data from each store and union the files to analyze performance across all your properties.
• Appending Historical Data: You may have an archive of last year's performance in one file and a live connection to this year's data. A union lets you append the historical data to see long-term trends.

The Rules: Requirements for a Successful Union

Tableau makes unions fairly intuitive, but it’s not magic. The process works smoothly only if your data tables follow a few key rules. Breaking these rules leads to mismatched data, null values, and general frustration. Before you start dragging and dropping tables, make sure they are structured similarly.

1. Consistent Number of Columns

Every table or sheet you want to union must have the same number of columns. If your January sales report has five columns (Date, OrderID, Product, Quantity, Revenue) and your February report has an extra sixth column for Discounts, the union will fail or produce messy, misaligned data.

2. Similar Column Names

Tableau cleverly matches columns based on their headers. If one file has a column named Sale Date and another has Date, Tableau will likely create two separate columns, one for each name, with lots of null values where the data from the other file should be. For the best results, ensure your column names are identical across all files.

3. Matching Data Types

The data type for corresponding columns should be the same. A Revenue column should be a number in every file. A Date column should be a date in every file. If Tableau finds a text value in a column it expects to be numeric, it will often convert the entire column to a string or show nulls, forcing you to clean it up manually.

Step-by-Step Guide: How to Create a Union in Tableau

Let's walk through the process of creating a union. We will use the common example of combining monthly sales data from different tabs in a single Excel file.

Step 1: Connect to Your Data Source

First, open Tableau Desktop and connect to your data. From the Connect pane on the left, select Microsoft Excel. Navigate to your file and click Open. This will bring you to the Data Source screen.

Step 2: Drag Your First Table to the Canvas

In the Data Source screen, you'll see a list of all the available sheets in your Excel file on the left. In our example, we have Jan_Sales, Feb_Sales, and Mar_Sales. Click and drag your first sheet, Jan_Sales, into the main canvas area that says "Drag tables here."

You will now see a preview of the data from the January sales sheet in the grid below.

Step 3: Drag and Drop to Create the Union

Now, to create the union, click and drag your second sheet, Feb_Sales, from the left pane and hover it directly below the Jan_Sales table already in the canvas. You'll see an orange-highlighted box appear with the text "Drag table to union." Release your mouse button to drop the table there.

That's it! Tableau has now appended the rows from Feb_Sales to Jan_Sales. You can repeat this process for Mar_Sales and any other tables you want to include.

Step 4: Review Your Unioned Data

Once the union is complete, you’ll notice two new fields have been automatically added on the far right of your data preview: Sheet and Table Name. These fields are generated by Tableau to help you identify the original source of each row of data. This is incredibly helpful for filtering or segmenting your analysis by the original file or sheet.

Bonus Tip: Using a Wildcard Union

If you're working with many similarly-named files in a single folder (like dozens of monthly CSV exports), adding them one by one is a huge pain. This is where the wildcard union comes in handy.

Instead of dragging individual tables, you can drag New Union from the left pane onto the canvas. From the dialog box, select Wildcard (automatic). You can then specify a search pattern that matches the files you want to union. For example:

• Sheet name pattern: If you use an Excel file with sheets like Q1_Sales, Q2_Sales, etc., you can use a pattern like *_Sales.
• File name pattern: If you have files like Sales_Data_2024_01.csv and Sales_Data_2024_02.csv, you could set up a connection to the folder and use a pattern like Sales_Data_*.csv to automatically pull in all matching files into a single union.

Troubleshooting Common Union Problems

Even when you think you've followed the rules, things can go wrong. Here are two of the most frequent issues and how to solve them.

Problem 1: Columns are Mismatched, Creating Nulls

This happens when column names are slightly different. For example, your January data has a column called OrderID while the February data has Order ID. Tableau sees them as two separate fields, resulting in a column for each peppered with null values.

The Fix: Manually merge the mismatched fields. In the data preview grid on the Data Source page, hold the Ctrl key (or Cmd on Mac) to select both columns. Right-click on one of the selected columns and choose Merge Mismatched Fields. Tableau will combine them into a single column, instantly cleaning up your data.

Problem 2: Incorrect Data Types

You might union two tables where the Date column in one is formatted correctly but in the other is just plain text. Tableau might get confused and default the entire unioned column to a string, preventing you from using it as a true date.

The Fix: You can change the data type directly in the Data Source screen. Click the icon at the top of the column header (it might show Abc for string or a calendar for date). From the drop-down menu, select the correct data type (e.g., Date or Date & Time). Tableau will attempt to convert all values in that column.

Final Thoughts

A union is one of the most practical tools in Tableau for anyone who deals with data that's spread across multiple files or sheets. By stacking datasets with a similar structure, you can easily consolidate information for comprehensive analysis. Mastering this skill is a quick way to streamline your data prep process and get to the insights faster.

That said, constantly downloading CSVs, cleaning them up, and managing complex unions can become a drain on time that you could be using for actual analysis. At Graphed, we automate all that tiresome prep work. By connecting directly to your data sources like Google Analytics, Shopify, and various CRMs, we bring it all into one place. From there, you just ask questions in plain English, and our AI builds live dashboards and reports for you in seconds. It saves you from the manual work of building unions and lets you focus on what the data actually means for your business.