Preparing and Combining Data

Your sales numbers live in one spreadsheet. Your customer list lives in another. The returns log sits in a third file that a coworker emails you every Monday. To answer a simple question like “which customers return the most,” you need all three in one place. On top of that, the spreadsheets are messy: one has a company logo and two blank rows above the real data, another crams “Jane Smith - West Region” into a single column, and a third stores each month as its own column heading instead of a tidy list of dates.

This is the everyday reality of data work. Before you can build a single chart, you often have to bring scattered tables together and tidy up the columns. Tableau gives you tools for both jobs, and this chapter walks through them in plain language.

Why this matters

A chart is only as trustworthy as the data underneath it. If you combine two tables the wrong way, Tableau will happily draw a beautiful, confident, and completely wrong picture. A common mistake doubles your sales totals because rows got duplicated during a merge, and nobody notices until a manager asks why the numbers do not match the accounting system.

Getting the preparation step right protects you from that. It also saves time: when your data is shaped well from the start, building views (the next chapter) becomes fast. When it is shaped badly, every chart becomes a fight.

Meet the Data Source page

When you connect Tableau to a file or database, you land on the Data Source page. Think of it as the staging area where you decide what data goes into your analysis and how the pieces fit together.

Two things happen here. First, you combine tables so related information sits together. Second, you do light cleanup so the columns are named and typed correctly. There are three ways to combine tables on this page (relationships, joins, and unions), plus a fourth method called blending that happens later, inside a worksheet. We will take them one at a time.

Relationships: the modern default

A relationship tells Tableau how two tables are connected without forcing them into one flat table. You just point out the shared field. For example, an Orders table and a Customers table both contain a Customer ID. You drag the second table next to the first, and Tableau draws a flexible line between them and asks which fields match. That line is nicknamed the “noodle” by Tableau users because of its loose, stretchy look.

Relationships became the default way to combine tables when Tableau introduced its data model in the 2020.2 release, and they are what Tableau recommends starting with today.

Here is why they are the safe first choice. Unlike a join, a relationship does not flatten your tables into one big table up front. Each table keeps its own identity, so Tableau remembers that a customer is one thing and an order is another. When you build a chart, Tableau looks at the fields you dragged in and figures out the right way to combine them for that specific question. Change the question, and Tableau adjusts. Because each table stays separate, relationships are much less likely to accidentally double-count your numbers.

If you are unsure how to combine two tables, start with a relationship.

Joins: merging into one table

A join is the older, more rigid way to combine tables. It merges two tables into a single wide table, matching rows by a shared field, and it locks that decision in before you build anything. Joins live one layer deeper than relationships, on what Tableau calls the physical layer. You reach it by double-clicking a table on the canvas to open the join canvas.

You choose how strict the match should be. Tableau offers four join types:

Inner join: keeps only rows that have a match in both tables. Anything without a match is dropped.
Left join: keeps every row from the left table, and fills in matches from the right table where they exist (blanks where they do not).
Right join: keeps every row from the right table, and fills in matches from the left table where they exist.
Full outer join: keeps every row from both tables, with blanks wherever a row has no match.

Here is a small example. Say you join a Customers table to an Orders table on Customer ID.

Customers                 Orders
+----+-------+            +-----+---------+--------+
| ID | Name  |            | Ord | CustID  | Amount |
+----+-------+            +-----+---------+--------+
| 1  | Ada   |            | 101 |   1     |  $40   |
| 2  | Ben   |            | 102 |   1     |  $25   |
| 3  | Cara  |            | 103 |   2     |  $10   |
+----+-------+            +-----+---------+--------+

Inner join on Customers.ID = Orders.CustID:

+----+------+-----+--------+
| ID | Name | Ord | Amount |
+----+------+-----+--------+
| 1  | Ada  | 101 |  $40   |
| 1  | Ada  | 102 |  $25   |   <- Ada now appears on TWO rows
| 2  | Ben  | 103 |  $10   |
+----+------+-----+--------+
        (Cara placed no orders, so an inner join drops her)

Notice two things. Ada placed two orders, so after the join her name shows up on two rows. If you now add up a “customer count” without care, Ada gets counted twice. This is the duplicate-row trap, and it is the single most common way joins produce wrong totals. Second, Cara had no orders, so an inner join removes her entirely. If you needed to see customers with zero orders, an inner join would hide them.

Joins are still useful, especially when tables truly belong together as one flat record. Just go in knowing that a join can quietly duplicate or drop rows, and always sanity-check your row counts afterward.

Unions: stacking similar tables

A union stacks tables on top of each other instead of matching them side by side. Picture three monthly sales files (January, February, March) that all have the exact same columns. A union pours all their rows into one long table, one after another.

For a union to work cleanly, the tables need the same structure: the same number of fields, with matching field names and matching data types. To create one on the Data Source page, double-click New Union in the left pane, then drag your tables into the box that appears, stacking each one below the last.

A quick way to remember the difference: a join adds columns (wider), a union adds rows (taller).

Data blending: combining at the sheet level

Sometimes your data sits in two genuinely separate systems that you cannot easily merge at the source. Maybe sales figures live in a company database and survey results live in a Google Sheet. Data blending combines this kind of data later, inside a single worksheet, on a common field.

Here is how it behaves. The first data source you use on a sheet becomes the primary source. When you pull a field from a second source onto the same sheet, that one becomes the secondary source, and Tableau links them on a shared field such as Region or Date. If the two sources have a field with the same name, Tableau links them automatically. If the names differ, you tell Tableau which fields to match. A small link icon in the data pane shows which field is doing the joining.

When does blending fit better than a join? Reach for blending when the data comes from two sources you cannot combine at the source, or when the sources are at different levels of detail (for example, daily sales in one and monthly targets in the other). Reach for a relationship or join when the data lives in one place and can be combined once, up front, on the Data Source page. Rule of thumb: relate or join when you can, blend when you must.

Reshaping and cleaning your columns

Combining tables solves the “spread across files” problem. The “messy columns” problem needs different tools.

Pivot fixes data that is stored wide when it should be tall. Imagine a spreadsheet with a column for each month: Jan, Feb, Mar, each holding a sales number. Tableau prefers one Month column and one Sales column instead. On the Data Source page, select the month columns, click the drop-down arrow next to a column name, and choose Pivot. Tableau turns the selected columns into two new fields named “Pivot Field Names” and “Pivot Field Values,” which you can then rename.

For light cleanup, a few moves cover most situations:

Rename a field by double-clicking its name and typing a clearer one.
Split a crowded field by clicking the drop-down arrow next to it and choosing Split (or Custom Split for control over the separator). This is how “Jane Smith - West” becomes a Name column and a Region column.
Fix the data type by clicking the small icon at the top of a column. Tableau uses Abc for text, # for a number, a calendar for a date, and a globe for geographic fields. If a date is being read as plain text, this is where you correct it.

There is also a helper for the classic messy Excel file. Data Interpreter scans a spreadsheet and tries to skip past the clutter (titles, notes, footers, blank cells, merged headers) to find the real table underneath. You turn it on with the Use Data Interpreter checkbox in the left pane of the Data Source page. It works with Microsoft Excel, text (.csv) files, PDF files, and Google Sheets. Always review what it did, since it is a smart guess and not a guarantee.

When to reach for Tableau Prep Builder

The tools above handle everyday tidying. For bigger cleaning and reshaping jobs, Tableau makes a separate application called Tableau Prep Builder, built specifically to combine, shape, and clean data before it ever reaches a chart.

Instead of one Data Source page, Prep Builder gives you a visual flow: a series of steps where you clean, join, union, pivot, filter, and fix values, seeing the results at each step as a preview. It is the right choice when the cleanup is heavy or repeated. Examples include stitching together a folder full of monthly files every month, standardizing messy text (turning “CA,” “Calif.,” and “California” into one value), or preparing the same complicated dataset again and again on a schedule.

You do not need Prep Builder to finish this course. Just know it exists, so that when a cleaning task starts to feel too big for the Data Source page, you know where the heavier tool lives.

Try It Practice a relationship using Tableau’s built-in sample data.

Open Tableau Desktop. On the start page under Connect, choose Sample - Superstore (a practice dataset that ships with Tableau).

You land on the Data Source page. In the left pane, find the Orders table and drag it onto the empty canvas.

Now drag the Returns table onto the canvas, dropping it next to Orders. A line (the “noodle”) appears between them.

Tableau opens a small panel asking how they relate. Under each table, pick Order ID so the shared field matches on both sides.

Look at the data. You have just told Tableau that orders and returns connect through Order ID, without merging them into one flat table.

What did you notice? Compare how little you had to decide here versus a join, where you would have had to choose a join type up front and risk duplicating rows.

In business

A regional manager wants to know which store locations have the highest return rates. The sales data lives in the company database, one row per order. The returns data arrives as a monthly spreadsheet from the operations team, one row per returned item, with a messy header block at the top.

The workflow: turn on Data Interpreter to strip the header clutter off the returns spreadsheet. Create a relationship between orders and returns on Order ID, so Tableau lines up each sale with its returns without flattening the tables and double-counting sales. If a “Store - Region” field is jammed into one column, Split it so store and region become separate fields you can filter on.

Fifteen minutes of preparation, and the manager’s return-rate chart is built on data that actually holds together. Skip it, and the same chart might show a return rate above 100 percent because a join duplicated the order rows.

Common pitfalls

Duplicated rows from a bad join. This is the big one. When a join matches one row on the left to several rows on the right, values on the left get repeated, and any total that adds them up gets inflated. If your numbers suddenly look too high after combining tables, suspect duplication first. Check your row count before and after, and prefer a relationship when you are unsure.
Confusing blending with joining. They sound similar and both “combine data,” yet they suit different situations. Joins and relationships combine tables at the source, on the Data Source page, when the data can be brought together once. Blending combines data at the sheet level, on a shared field, when the sources are separate or at different levels of detail. Using blending where a relationship would do (or the reverse) leads to confusing results and slow workbooks.
Forcing a union onto mismatched tables. A union expects the same columns in the same order with the same data types. Stack tables that do not truly match, and you get a jumble of half-filled columns.
Trusting Data Interpreter blindly. It is a helpful guess at where your real data starts, not a certainty. Always glance at the result to confirm it grabbed the right rows.

Practice

Open Sample - Superstore in Tableau Desktop and try each of these:

Create a relationship between the Orders and Returns tables on Order ID (from the Try It above), then remove it and rebuild it as a join instead. Notice where you had to choose a join type.
Find a field that holds two pieces of information and Split it into two fields.
Pick any text field being read with the wrong type and change its data type using the icon at the top of the column.
In one sentence, write down when you would reach for Tableau Prep Builder instead of the Data Source page.

Next up, chapter 4 takes your prepared data and turns it into actual views: the charts, tables, and maps you drag together on the worksheet.

Part of the Tableau track. Developed with AI assistance and reviewed by a human editor. Tableau changes often; menu names, features, and prices may have shifted since the “checked on” dates in this chapter. Verify anything critical against Tableau’s official documentation.