How to Merge Two Sheets in Tableau

Bringing two separate datasets together is one of the most fundamental tasks in data analysis. Tableau offers several powerful ways to merge sheets, but choosing the right method is essential for getting accurate results. This guide will walk you through the three primary methods for merging data in Tableau - joins, unions, and data blending - and help you understand when and how to use each one.

First, Prepare Your Data for Merging

Before you even think about merging sheets, a little prep work can save you a lot of headaches. A clean, well-understood dataset is the foundation of any reliable analysis. Take a few minutes to check these three things:

• Identify Common Fields: For methods like joining or blending, you need a "key" - a common field that exists in both of your datasets. This is how Tableau knows which rows in one sheet correspond to rows in the other. Common examples include ‘Customer ID,’ ‘Order ID,’ ‘Product SKU,’ or a date field. Make sure the column names are consistent if possible, though Tableau can join on columns with different names.
• Check Data Types: The common fields you plan to use for your merge should have the same data type. A field can't be a number in one sheet and a string (text) in another. In Tableau’s Data Source pane, you can see a small icon above each column name indicating its data type (e.g., # for numbers, Abc for strings). If they don't match, you can click the icon and change it.
• A Quick Clean-Up: Check for trailing spaces, inconsistent capitalization (e.g., "USA" vs. "usa"), or different date formats. These small inconsistencies can prevent data from merging correctly. Using Tableau's built-in functions like TRIM() or UPPER() can help clean these up quickly.

Method 1: Joins (Adding Columns Sideways)

Think of a join as putting two puzzles together side-by-side. You're adding more columns (information) to your existing rows based on a shared piece of data. This is the most common way to merge sheets when your data lives in the same data source, like two different sheets in a single Excel workbook or two tables in the same SQL database.

For example, you might have one sheet with Sales Data (Order ID, Product ID, Sale Amount) and another with Product Details (Product ID, Product Name, Category). A join lets you bring the product name and category into your sales data sheet, so you can analyze sales by category.

Understanding the Four Types of Joins

Tableau uses Venn diagrams to represent joins, which is a surprisingly perfect analogy.

1. Inner Join

An Inner Join only returns records that have a match in both sheets. If a row in your first sheet doesn't have a corresponding value in the second, it gets dropped. This is great when you only want to analyze data that is complete across both tables.

• Example: If you inner join Sales and Product Details on ‘Product ID,’ only sales records for products that actually exist in your Product Details sheet will be kept. Any sales for a discontinued or mistyped Product ID will be excluded from your final dataset.

2. Left Join

A Left Join returns all records from the left sheet and only the matching records from the right sheet. If a row from the left sheet has no match in the right sheet, the new columns from the right sheet will simply show up as 'Null'.

• Example: With a left join on Sales (left) and Product Details (right), you would see every single sale. If a sale was made for a product not listed in your details sheet, you’d still see the sale amount, but the 'Product Name' and 'Category' columns would be null for that row. This is often the safest and most commonly used join.

3. Right Join

A Right Join is the mirror image of a Left Join. It returns all records from the right sheet and only the matching records from the left. If a record in the right sheet has no match, the columns from the left sheet will be 'Null'.

• Example: A right join on Sales (left) and Product Details (right) would show every single product in your catalog. If a product has never been sold, you'd still see its details, but the 'Order ID' and 'Sale Amount' columns would be null. This is useful for finding products that aren't selling.

4. Full Outer Join

A Full Outer Join returns every record from both sheets. If there’s a match, the data is combined. If there’s no match, the missing side's columns will be filled with 'Null'.

• Example: A full outer join would give you every single sale record and every single product detail. You'd see sold products, unsold products, and any sales that don't match a listed product. This can create a very large dataset but is useful for a complete audit of all your data.

How to Create a Join in Tableau

Let's walk through creating a simple Left Join.

1. Connect to your data source (e.g., an Excel file with multiple sheets).
2. Drag your first sheet (this will be your "left" table) into the canvas. Let’s say it’s 'Sales Orders.'
3. Now, drag your second sheet, say 'Customer Details,' and drop it onto the canvas, to the right of the first one.
4. Tableau will automatically try to create a join. A Venn diagram icon, called the "Join Noodle," will appear.
5. Click on the Join Noodle. A configuration box will pop up.
6. Here you can select the type of join (Inner, Left, Right, Full Outer) and confirm the common field(s). Tableau is pretty smart about guessing the common fields, but if it gets it wrong, you can click on the field names to select the correct "join clause." For example, join on 'Customer ID' from the orders sheet and 'ID' from the customer sheet.
7. That's it! Your data is joined. You can see the combined result in the data grid below the canvas.

Method 2: Unions (Stacking Rows on Top of Each Other)

Where joins add data horizontally by adding columns, unions combine data vertically by stacking rows. You use a union when you have multiple sheets with the exact same structure (same column names and data types) but containing different sets of records. Think of monthly sales reports that are split into separate tabs: 'Jan_Sales,' 'Feb_Sales,' 'Mar_Sales,' and so on.

How to Create a Union in Tableau

Creating a union is even simpler than creating a join.

1. Connect to your data source.
2. Drag your first sheet (e.g., 'Jan_Sales') into the canvas.
3. Now, drag your second sheet ('Feb_Sales') and hover it directly below the first sheet in the canvas. You'll see a vibrant orange drop zone appear with the text "Drag table to union."
4. Drop the sheet there. The data from both sheets is now stacked into a single table. Tableau automatically adds two helpful columns: 'Path' and 'Sheet,' so you can still tell which rows came from which original source.
5. You can keep dragging more sheets to add them to the union.

Pro Tip: If you have many files (e.g., monthly transaction CSVs saved in a folder), you can use a Wildcard Union. Instead of adding a single sheet, you can tell Tableau to automatically scan a directory and union all files that match a certain pattern (like '*.csv').

Method 3: Data Blending (A Special Kind of Left Join)

What if your data isn't in the same place? Maybe you have sales data from your Shopify store and marketing campaign data from a Google Sheet. They are two completely separate data sources. You can't join them directly in Tableau's data source pane. This is where Data Blending comes in.

Data blending is Tableau's way of querying data from multiple sources in a single worksheet. It operates on a sheet-by-sheet basis within a workbook. Conceptually, it works like a Left Join, but there are some important differences in how it operates behind the scenes.

First, it aggregates the data from the secondary source before bringing it over, which can be useful when your data is at different levels of granularity (e.g., transactional sales data vs. a daily marketing budget).

How to Create a Data Blend in Tableau

1. Connect to your first data source (let's say, your Shopify export). This will be your primary data source.
2. Go to a worksheet and build a simple view by dragging a dimension (like ‘Date’) to the columns shelf. You'll notice this data source has a blue checkmark next to its name.
3. Next, go to the Data menu and select "New Data Source" to connect to your second source (e.g., your Google Sheet with marketing spending). This will be your secondary data source.
4. You'll now see both data sources listed in the Data pane on the left. The secondary source should have an orange checkmark next to it.
5. In your secondary data source, find the common field you want to link on (e.g., 'Date'). Tableau will look for a small link icon (a broken chain) next to a field name if it recognizes a common field. If the icon shows a broken link, click it to establish the relationship.
6. Now, you can drag measures from your secondary (orange) data source into your view. For instance, drag 'Marketing Spend' onto the rows shelf alongside your 'Sales' measure from the primary source.
7. Your view is now showing data from both sources, blended on the common 'Date' field.

A common sign of a blending issue is seeing asterisks (*) instead of values. This usually means the data doesn't align at the level of detail in your view. For example, if you blend sales and budget by 'Date' but only have one budget number for the month, you may see an asterisk when viewing data by day because Tableau doesn't know how to attribute that one budget number to multiple days.

Which Method Should You Choose? A Quick Guide

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

• Use a JOIN when:
• Use a UNION when:
• Use a DATA BLEND when:

Final Thoughts

Learning how to effectively merge data is a major step in becoming proficient with Tableau. By understanding the core differences between joins (horizontal combining), unions (vertical stacking), and blends (cross-source aggregation), you can tackle virtually any data scenario. Start with a clean dataset, choose the method that fits your goal, and you'll be building insightful, multi-source dashboards in no time.

While tools like Tableau are powerful, the initial setup of connecting, cleaning, and merging data from different marketing or sales platforms can consume hours of your week. Often, the real bottleneck isn’t building the chart - it’s preparing the data so you can build the chart in the first place. This is an area where we saw an opportunity to make data analysis much simpler. With Graphed, we handle the drudgery by integrating with your data sources like Google Analytics, Shopify, and Salesforce directly. Instead of spending time in data source panes setting up joins and blends, you can just ask a question in plain English, and a live, interactive dashboard gets created for you in seconds, with all sources already connected.