How to Merge Tables in Tableau

Combining data from different tables is a fundamental step in building any meaningful report in Tableau. This article will walk you through the four key ways Tableau merges data: relationships, joins, blending, and unions. We'll break down what each one does, when to use it, and how to set it up.

Why Merge Tables in the First Place?

Data rarely lives in one perfect, giant table. More often, it's split into multiple tables to keep it organized and efficient. For example, your company might have:

• A Sales Table with transaction details like OrderID, ProductID, Quantity, and SaleDate.
• A Products Table with product details like ProductID, ProductName, and Category.
• A Customers Table with CustomerID, CustomerName, and Region.

To answer a question like, "Which product category sells best in the West region?" you need information from all three tables. Merging allows you to link these separate sources based on common fields (like ProductID or CustomerID) to create a unified view for analysis.

Method 1: Relationships (The Modern Way)

Relationships are Tableau’s newer, smarter, and recommended way to combine tables from the same data source. Think of them less as merging tables and more as teaching Tableau how your tables are related to each other. Tableau then uses that context to pull in data from different tables only when you need it for a specific visualization.

This "on-demand" approach is incredibly efficient and avoids many common issues like data duplication that can occur with traditional joins.

When to Use Relationships:

• This should be your default method for combining tables from the same data source (like multiple tables in an Excel file or a SQL database).
• When your tables are at different levels of detail (e.g., daily sales data and monthly customer targets).
• When you want the most flexibility without committing to a fixed, flattened table structure.

How to Create a Relationship in Tableau:

1. Connect to your data source. You'll see the Data Source page.
2. Drag your first or primary table (e.g., Sales) onto the canvas. It's often called the "fact table" because it contains the measurements or metrics you're analyzing.
3. Drag your second table (e.g., Products) onto the canvas. Tableau will automatically try to create a relationship based on a common field name.
4. A line, affectionately called a "noodle," will appear connecting the two tables. Click on this noodle to see and edit the relationship details.
5. Tableau will have guessed the common field (like ProductID = ProductID). If it's wrong, you can manually select the correct fields for the relationship.
6. Add more tables (like Customers) and repeat the process.
7. Once your relationships are defined, click on a worksheet tab. You can now pull fields from any of the related tables into your rows, columns, and marks cards to build views.

The beauty of relationships is that each table remains separate and distinct. When you build a visualization, Tableau automatically creates the appropriate join in the background, minimizing data duplication and improving performance.

Method 2: Joins (The Traditional Way)

If you're familiar with SQL, you already know about joins. A join physically combines tables into a single, new (and often wider) table based on a shared field, known as the join key. Unlike relationships, this merge happens before your analysis begins, creating a fixed, flattened data structure.

Opening up the data source and double-clicking on your main logical table on the canvas will bring you to the physical layer. This is where you can manually set up joins, if needed.

Types of Joins in Tableau:

Tableau supports four main types of joins:

• Inner Join: Returns only the rows where the join key exists in both tables. If a product ID from your Sales table doesn't exist in your Products table, that sales record will be dropped entirely.
• Left Join: Returns all the rows from the left table and only the matching rows from the right table. If a product in your sales data had no match in the product's details table, you would get null values for the product's detail fields, but you would see every sale.
• Right Join: The opposite of a left join. It returns all rows from the right table and the matching rows from the left. This might be useful to analyze what products' details entries didn't get purchased.
• Full Outer Join: Returns all rows from both tables. If there's no match, the columns from the non-matching side will be filled with NULL.

When to Use Joins:

• When you know for certain that you need a single, fixed table for your analysis.
• If you are working with an older version of Tableau or have a very specific use case where a relationship won't work.
• Be mindful: misused joins, especially with data at different grains (e.g., joining daily sales to monthly targets), can lead to massively duplicated data and incorrect aggregates.

How to Create a Join in Tableau:

1. On the Data Source page, drag your first table to the canvas.
2. This is the key step: Double-click that table on the canvas. This opens the "physical layer" where you can create traditional joins.
3. Drag your second table directly next to the first one. A Venn diagram icon for an inner join will appear by default.
4. Click on the Venn diagram icon to open the join configuration.
5. Select the type of join you need (Inner, Left, Right, Full Outer).
6. Define the join clause by selecting the common field from each table (e.g., Sales.ProductID = Products.ProductID).

Method 3: Data Blending (For Different Data Sources)

What if your sales data is in a SQL database, but your customer targets are in a Google Sheet? You can't use relationships or joins because the data lives in entirely different sources. This is exactly where data blending comes in.

Data blending is Tableau’s method for combining data from multiple, distinct data sources within a worksheet. It works by querying each data source independently and then blending the aggregated results together in the final visualization.

Key Concepts for Data Blending:

• Primary Data Source: The first data source you use in a view (by dragging a field onto the canvas). It gets a blue checkmark icon.
• Secondary Data Source: Any subsequent data source you use in the same view. It gets an orange checkmark icon.
• Linking Field: A common field between the two data sources that tells Tableau how to blend the data. A broken link icon will appear if Tableau can't find a good link, click it to define the relationship manually.

When to Use Data Blending:

• When your data is in separate, published Tableau Server/Cloud data sources.
• When you need to combine data from different databases or data providers (e.g., blending Google Analytics data with your Salesforce CRM data).
• When you need to combine data at different levels of detail and a join would cause massive duplication.

How to Blend Data in Tableau:

1. Connect to your first data source (e.g., Salesforce). Then, go to Data > New Data Source and connect to your second source (e.g., Google Sheets). You'll see both data sources listed in the Data pane.
2. Select your primary source and start building a view. For example, drag Customer Name from Salesforce onto the Rows shelf.
3. Now, click on your secondary source in the Data pane. Tableau will try to identify a common "linking field." You'll see a small link icon next to the field it found (e.g., Customer Name).
4. If the link icon is broken or missing, you must manually define the link via Data > Edit Blend Relationships.
5. Drag a measure from the secondary source (like Sales Target from the Google Sheet) into the view. Tableau will aggregate the data from the secondary source at the level of the linking field dimensions from your primary source.

A key limitation to remember: data from the secondary source must always be aggregated. You can't bring in row-level details from a secondary source.

Method 4: Unions (For Stacking Data)

While relationships, joins, and blending combine tables by adding more data columns, a union combines tables by stacking them on top of each other to add more data rows.

The key requirement for a union is that the tables must have the same (or very similar) column structure. A classic example is combining monthly sales files - January_Sales.csv, February_Sales.csv, and March_Sales.csv - into a single, unified sales table for the quarter.

When to Use Unions:

• When you have data split across multiple files or tables that all share the same layout.
• Examples: combining weekly event logs, monthly budget sheets, or regional sales reports.

How to Create a Union in Tableau:

1. On the Data Source page, drag your first table (e.g., January_Sales) to the canvas.
2. Now, drag the second table (e.g., February_Sales) and drop it directly onto the first table. An option to "Union" will appear.
3. A + sign will appear on the table, indicating it's a union of multiple tables. Click the dropdown on the table to see or edit which tables are included.
4. You can add tables manually, or even better, use a wildcard search. If your files have a consistent naming convention (like Sales_2023_*.csv), you can create a wildcard union to automatically include all matching files in a directory. This is great for data that is periodically updated with new files.

Tableau will also generate two extra columns in a union: Table Name and Path, helping you identify which file each row originated from.

Which Method Should You Choose? A Quick Guide

Still not sure which option is right? Here’s a simple cheat sheet:

• For combining data from the same database/file: Start with Relationships. This is the new best practice and offers the most flexibility.
• For combining data from completely different sources (e.g., SQL + Google Sheets): Use Data Blending.
• For stacking data from files with the same columns: Use a Union.
• For specific, old-school reasons where you need a single, fixed table before analysis: Use a Join, but proceed with caution.

Final Thoughts

Understanding how to merge data is an essential skill for getting the most out of Tableau. Relationships, joins, blends, and unions are the four primary tools at your disposal, and choosing the right one for your scenario will make your analysis faster, more accurate, and more powerful.

While mastering data connections in a single tool is a huge step, the challenge often expands when you need to pull insights from 5, 10, or more different cloud applications. We built Graphed to solve exactly that problem. Imagine connecting your Google Analytics, Salesforce, Shopify, and Facebook Ads accounts in seconds, and then asking in plain English: "Show me a dashboard of my marketing funnel, from ad spend to final sale." Graphed automates the heavy lifting of data connection and dashboard creation so you can get straight to the answers you need.