How to Blend Tableau

Combining data from different places is one of the most common - and often frustrating - parts of data analysis. You have your sales figures in one system, your marketing ad spend in another, and your website traffic in a third. Tableau's data blending feature is designed to solve this by letting you bring together data from multiple sources into a single visualization. This guide will walk you through exactly what data blending is, when to use it, and how to do it step-by-step.

What is Data Blending in Tableau?

Data blending is a method for combining data from different sources on a single Tableau worksheet. Think of it as a way to create a temporary, worksheet-specific connection between two or more datasets. You designate one data source as the primary source and any subsequent ones as secondary sources. Tableau then queries each data source independently, aggregates the results, and displays them together in your visualization.

The key here is that the data is combined after an initial aggregation in each source. This is perfect for when your data is at different levels of granularity - like combining daily sales data with monthly sales quotas.

Data Blending vs. Relationships and Joins

It's easy to confuse blending with Tableau's other data combination methods: relationships and joins. Here’s a simple breakdown of the difference:

• Joins: Joins physically stitch tables together row-by-row at the data source level before any analysis begins. This creates a new, wider, single table of data. Joins are best when your data lives in the same source (like different tables in a single SQL database) and you can define clear matching criteria.
• Relationships: Introduced in newer versions of Tableau, relationships are a more flexible way to define connections between tables from one or more data sources. They're like "smart" joins that happen behind the scenes based on the fields you use in a viz, preserving the original level of detail in each table and preventing data duplication issues. This is often the recommended first approach.
• Data Blending: Blending is your go-to method when joins or relationships aren't possible or practical. It operates at the worksheet level, querying each data source separately and then bringing the aggregated results together through shared dimensions called "linking fields." You could think of its behavior as similar to a left join, where all data from the primary source is included, along with matching data from the secondary source.

When Should You Use Data Blending?

Data blending is the right tool for specific scenarios where other methods fall short. You'll find it most useful when you need to:

• Combine data with different levels of detail (granularity). This is the classic use case. For example, you might have transactional sales data recorded daily, but company-wide sales targets set on a monthly or quarterly basis. You can’t join this data row-by-row, but you can blend it on a common field like "Month" or "Region."
• Combine data from published Tableau Server/Cloud sources. Once a data source has been published to your Tableau Server or Cloud site, you can't join it directly with another source. Blending allows you to combine these published sources in a new workbook.
• Analyze data across completely different systems. If your CRM data is in Salesforce and your website data is in Google Analytics, you can’t perform a database-level join. Data blending lets you bring aggregates from both sources together by linking on a common field, like the UTM Campaign name.
• Quickly combine small, secondary datasets. Sometimes you just need to pull in a bit of extra information from a simple spreadsheet, like mapping product codes to product categories or sales reps to regions. Blending is a fast way to do this without having to modify your primary data source.

The Core Concepts: Primary, Secondary, and Linking Fields

To successfully blend data, you need to understand three core concepts that Tableau uses to manage the connection.

1. Primary Data Source

The primary data source is the first data source you use on a worksheet. Whichever dataset you drag a field from first determines the primary source for that specific view. Tableau highlights the primary data source with a blue checkmark icon in the Data pane. All dimensions from this source can be used in the view, and they set the base level of detail for your visualization.

2. Secondary Data Source

The secondary data source is any other source you bring into the same worksheet. It will be marked with a little orange checkmark icon. You can only use dimensions from the secondary source if they're also "linking fields" (more on that next). Measures, however, can be pulled in and will be aggregated based on the linking fields.

3. Linking Fields

A linking field is a dimension that exists in both your primary and secondary data sources. This is the bridge that tells Tableau how to match up the aggregated data from each source. Tableau automatically tries to identify linking fields by finding dimensions with the exact same name. If it finds one, you'll see a small chain-link icon (🔗) next to the field in the secondary data source's pane. If it's a broken link (🗙), you'll need to manually define the relationship.

Step-by-Step Guide: How to Blend Data in Tableau

Let's walk through a practical example. Imagine we have two separate data sources:

1. A Store Sales dataset (in a SQL database) that contains daily sales figures by Store ID, Date, and product.
2. An Store Targets Excel spreadsheet that lists the monthly sales Target for each Store ID.

Our goal is to create a bar chart that compares the total actual sales to the monthly target for each store.

Step 1: Connect to Both Data Sources

First, open Tableau and connect to both data sources. Add a connection to your SQL database containing the store sales, and then add a second connection to the Excel file with the store targets. You'll see both data sources listed in the top-left Data pane.

Step 2: Establish Your Primary Source

First, go to a new worksheet. The most important step in blending is deciding on your primary source. Since we want to display all stores and their sales, regardless of whether they have a target, our Store Sales data should be the primary source.

To set it, simply click on the Store Sales source in the Data pane and drag the Store ID dimension onto the Rows shelf. Just by doing this, Tableau automatically designates Store Sales as the primary (blue checkmark) source for this worksheet.

Step 3: Introduce the Secondary Source and Check the Link

Now, click on the Store Targets data source in the Data pane. Tableau automatically looks for a potential linking field. In our case, both sources have a dimension named Store ID. Tableau will recognize this match and activate the link.

You’ll notice two things:

• The Store Targets source now has an orange checkmark.
• The Store ID dimension within the Store Targets source has a small chain-link icon (🔗) next to it, indicating it's the active linking field.

If the dimension names were different (e.g., Store ID in one and StoreID in the other), the link icon might appear broken. You would simply click the broken link icon to open a dialog box and manually define the relationship between the two distinct fields.

Step 4: Bring in Fields from Both Sources

With the primary source and link established, you can now build your visualization. Drag the Sales measure from your primary Store Sales data source onto the Columns shelf. You'll see a bar chart showing total sales for each store.

Next, switch back to the secondary Store Targets data source and drag the Target measure onto the Columns shelf. Tableau will now display two bars for each store: one for the SUM(Sales) and one for the SUM(Target).

Step 5: Handle Data Granularity and Potential Asterisks

Sometimes when you bring in a field from a secondary source, it may appear as an asterisk (*). This happens when a single record in your primary source (like one store) matches multiple records in your secondary source. Tableau doesn't know which one to show, so it displays an asterisk.

A common fix is to use the ATTR() aggregation. Right-click the field with the asterisk and change its aggregation type to "Attribute". ATTR(field) tells Tableau: "If all matching records in the secondary source have the same value, show that value. If they have different values, show an asterisk." This is often a good indicator that your level of detail is mismatched.

Common Blending Pitfalls and Best Practices

While powerful, data blending has its nuances. Keep these best practices in mind to avoid common issues.

• Relationships/Joins are often better: If you can connect your data using relationships or joins at the data source level, do that first. These methods are typically more performant and offer greater flexibility than blending. Save blending for when those options are off the table.
• Mind your cardinality: Linking fields with a high number of unique values (high cardinality), like customer_id or transaction_id, can slow down your worksheet's performance significantly. If possible, blend on fields with lower cardinality, like Region, Product Category, or Month.
• Primary filters matter most: A filter applied to your primary data source affects the entire view. However, a filter applied to a field from the secondary data source will only filter the data from that source without affecting the primary data being displayed.
• Use consistent naming: Keep linking field names identical across your various data sources. While you can manually define links, consistent naming conventions allow Tableau to do the work for you automatically, saving time and reducing potential errors.

Final Thoughts

Data blending is a valuable skill in your Tableau toolkit, giving you the power to combine different datasets directly in a worksheet when formal joins or relationships aren't practical. By understanding the roles of primary and secondary sources and ensuring your linking fields are correctly set up, you can create unified dashboards from disparate systems like databases, spreadsheets, and cloud applications.

Managing and combining data from many different sources - Google Analytics, Shopify, Salesforce, Facebook Ads - is exactly why reporting can become so time-consuming. Instead of wrestling with blending fields in complex tools, we built Graphed to simplify this entire process. You connect your data sources in a few clicks, and then you can ask for charts and dashboards in plain English. For example, just ask, "Show me a dashboard of Shopify revenue vs. Facebook Ads spend by campaign," and our AI data analyst builds it in seconds, without you needing to worry about primary sources, joins, or linking fields at all.