How to Combine Two Data Sets in Tableau

Bringing two tables together is a fundamental step in nearly every data analysis project. In Tableau, you have multiple ways to combine data sets, and choosing the right one can make the difference between a clean, accurate dashboard and a confusing mess of duplicated numbers. This guide will walk you through the key methods - Relationships, Joins, and Blending - explaining how to execute each one and when it makes the most sense to use it.

First Things First: Understand Your Data

Before you drag any tables onto the canvas, take a moment to get familiar with your data. Skipping this step is the fastest way to run into problems down the line. Ask yourself two simple questions:

1. What does one row represent? This is known as the level of detail or granularity. For example, in a sales dataset, does each row represent a single transaction, a customer, or a product? In another dataset of customer support tickets, does each row represent one ticket or one customer interaction? Knowing the granularity of each table you want to combine is essential.
2. What field do these tables have in common? To connect two data sets, you need a shared dimension, often called a "key." This could be a CustomerID, OrderID, Email Address, or a Product SKU. This common field is the bridge that will link your tables together.

Imagine you have one table with order information (OrderID, OrderDate, CustomerID, Amount) and another with customer details (CustomerID, CustomerName, Region). The CustomerID is the clear link between them. Understanding this relationship upfront makes the combining process in Tableau much clearer.

The Three Ways to Combine Data in Tableau

Tableau offers three primary methods for meshing data together. Each serves a different purpose, and the one you choose depends on your specific data and goals.

• Relationships: This is Tableau's modern, default, and recommended method. Relationships are flexible and smart. You tell Tableau which fields connect your tables, and Tableau figures out the appropriate join type behind the scenes based on the visualization you’re building. It keeps your tables separate but related, which prevents common data duplication issues.
• Joins: This is the traditional, physical database-style approach. Joins merge multiple tables into a single, new, wider table before analysis begins. This method is more rigid and requires you to define the exact join type (inner, left, right, outer) upfront. It's useful in certain scenarios but can easily lead to data duplication if the table relationships are complex.
• Data Blending: This method works at the sheet level and feels more like a post-query stitch. You query each data source independently, and then Tableau aggregates and links the results in your worksheet. It's best used when your data is at different levels of granularity or when you need to combine data from sources that can't be joined at the database level.

For most situations today, starting with Relationships is the best practice. Let’s dive into how to set them up.

How to Combine Data with Relationships

Relationships are Tableau’s preferred method because they preserve the original structure and granularity of your tables, leading to more accurate analysis with less hassle.

Step-by-Step Guide to Creating Relationships

Let's use our example of Sales Data and Customer Information.

1. Connect to Your First Data Source

Start by opening Tableau and connecting to your first data file (e.g., an Excel file containing your sales transactions). Tableau will display the sheets in the file. Drag the "Sales" sheet onto the canvas. This is now your primary logical table.

2. Connect to and Add Your Second Data Source

Next, add your second data source containing customer details. You can do this by clicking "Add" next to Connections in the Data Source pane. After connecting, drag the "Customer Info" table onto the canvas near the first one.

3. Let Tableau Create the Relationship

Tableau will automatically try to identify a common field between the two tables. If it finds one (like CustomerID), it will instantly create a relationship, visually represented by a curved line or "noodle" connecting the two tables.

4. Configure the Relationship (If Needed)

If Tableau didn't create the relationship automatically or used the wrong fields, you can configure it manually. Click on the noodle to open the "Edit Relationship" dialogue box. Here you can:

• Select the correct fields from each table to build the relationship (e.g., CustomerID = CustomerID).
• Add more relationship clauses if multiple fields are needed.
• Adjust Performance Options like "Cardinality" (One-to-many, many-to-many) and "Referential Integrity" to give Tableau more context for optimizing queries. For most users, the default settings work just fine.

Once related, you can go to a worksheet and start building visualizations. You can pull Region from the Customer Info table and Amount from the Sales table, and Tableau handles joining them correctly in the background to show you Sales by Region.

How to Combine Data with Joins

Sometimes you need to create a single, defined table before any analysis starts. This is where joins come in handy. Keep in mind that setting up a join physically merges the tables, which can affect your data's structure.

Step-by-Step Guide to Creating Joins

To create a join, you must open the "physical layer" within a logical table.

1. Prepare the Join Canvas

Drag your first table onto the canvas (e.g., "Customers"). Then, double-click on that table icon. This opens up a new view where you can physically join other tables to it.

2. Add the Second Table to Join

Drag your second table (e.g., "Orders") into this physical canvas area. Tableau will display a Venn diagram icon between them, representing the join.

3. Choose Your Join Type

The Venn diagram is your key to control. Click it to choose from the four types of joins:

• Inner Join: Shows only records where the key (e.g., CustomerID) exists in both tables. Use this to see only customers who have placed an order.
• Left Join: Shows all records from the left table (Customers) and any matching records from the right table (Orders). Use this to see all your customers, whether or not they've placed an order. Non-ordering customers will have null values for the order fields.
• Right Join: Shows all records from the right table (Orders) and any matching records from the left (Customers). Not as common, but useful if you wanted to see all orders, even if a customer record was somehow missing.
• Full Outer Join: Shows all records from both tables. Use this when you want to see every customer and every order, regardless of whether they match.

After selecting your join type and the join clause (the fields you're joining on), Tableau presents your data as a single flattened table in the data grid below.

Heads Up on Data Duplication: If one customer has five orders, a join will create five rows, and each row will repeat that customer’s details. This duplication can inflate metrics like customer count or average purchase value if you’re not careful. This is a primary reason relationships are now the preferred method.

How to Combine Data with Blending

Data blending is your go-to when your data is at different aggregation levels or from entirely separate data sources that can't be joined (like Google Analytics and a SQL database).

Step-by-Step Guide to Data Blending

Blending happens at the worksheet level, not the data source level.

1. Connect Multiple Data Sources

On the Data Source page, connect to each data source you need individually. For example, add "Monthly Sales Targets" and then add "Daily Actual Sales." Do not create a relationship or join here. Head straight to a worksheet.

2. Designate a Primary Data Source

A "primary" data source is simply the first one you use in a visualization. In your worksheet, select your "Daily Actual Sales" source from the Data pane. Drag Date to columns and Sales to rows. Your daily sales are now the primary data source for this sheet, marked with a blue checkmark.

3. Activate the Secondary Data Source

Now, click on the "Monthly Sales Target" data source in the Data pane. Tableau will automatically try to find a linking field, marked with a chainlink icon. If the names match (e.g., Date), it will likely auto-link them. If not, link them manually. Make sure the link is active before you use it.

Once linked, your monthly targets source will be shown with an orange checkmark on the bottom left, which means it's now the secondary data source.

4. Combine Fields in Your View

With the primary source still selected and the date field link active, drag your SalesTarget measure from your target data source to your chart. Your visualization now shows day-by-day sales data against a monthly target value, which appears as a flat line over each month. Data blending manages to combine these data sets at different levels of granularity correctly.

Final Thoughts

You've seen the three powerful ways to combine data in Tableau: the flexible and modern Relationships, the rigid and traditional Joins, and the specialized case for Data Blending. Understanding how and when to use each method is key to creating accurate, powerful, and performant dashboards. Start with Relationships as your default approach, and turn to Joins and Blending only when there's a specific reason to do so.

Connecting various data sources together - whether in Tableau, a spreadsheet, or another BI tool - is often where most analysis projects slow down. At Graphed, we built our entire platform to automate this headache. You can connect all of your marketing and sales sources - like Google Analytics, Shopify, Salesforce, Facebook Ads - and our AI handles the data modeling for you. You no longer have to worry about how to combine tables, you can use natural language questions like "which channels are driving revenues" and get live visualizations and answers in seconds.