Skip to content
Tutorials Center
My learning

Topics

Lesson 7 of 35 ยท Connecting to Data

Tableau Data Blending, Splitting Text, Pivoting and Data Cleaning

Learn how Tableau data blending works, when to use it instead of a join, and how to clean data with split, pivot, aliases, null handling and Data Interpreter.

Two skills finish the "Connecting to Data" chapter. Data blending combines data from separate data sources at the level of a view. Data cleaning makes a messy source usable: splitting text into columns, pivoting wide tables, fixing labels and handling nulls. Both come up on almost every real project.

Data blending

A blend uses two or more separate data sources, one primary and one or more secondary. You build a view with fields from the primary source, drop in a field from the secondary source, and Tableau connects them through linking fields (also called blend relationships).

How it works, step by step:

  1. Create a view from the primary source (for example Sum(Sales) by Region from your orders).
  2. Click a second data source in the Data pane. A field with the same name in both sources gets an orange chain link icon: that is the link field. (If names differ, use Data > Edit Relationships... and define the link manually.)
  3. Drag a measure from the secondary source (for example Target from a targets file) onto the view. The secondary source's name is shown with an orange check mark; the primary has a blue check mark.

The key behavior: Tableau aggregates each source separately at the level of the link field, then combines the results, much like a left join of two summary tables. The primary source decides which rows appear. Unmatched primary marks show Null for the secondary measure.

Primary source

Drives the view Its dimensions create the marks
Orders data Region, Category, Sales

Blue check mark in the Data pane.

1 / 3

Try it: a blend as two summaries and a left join (stand-in)

This SQL stand-in does what the blend does: it summarizes the orders by the link field, summarizes the targets by the same field, and left-joins the second result onto the first.

SQL Stand-in: blend orders with targets by Region

Press Run (or Ctrl + Enter) to see the result here.

South has no target, so it shows NULL, exactly as an unmatched blend mark does. Notice that East's two target rows were summed before matching, so East is not counted twice.

Blend or join?

Use a blend when Use a join or relationship when
Data sources are separate and cannot be joined at the database (different servers, a published source, a spreadsheet of targets) Tables are in the same connection
The secondary data is at a coarser level than the primary (region targets versus order rows) You need row-level matching
You want a left-join effect on aggregated results You need filters on the second table to affect the first

Blending limitations: only the primary source's filters work naturally; secondary measures are aggregated and you cannot use COUNTD on a secondary field in all versions; the blended view cannot use row-level calculations that mix both sources. If the sources can be related, relationships are a better choice.

Data cleaning

Real data arrives with missing values, merged columns and inconsistent names. Fix problems as early as possible, ideally at the Data Source page, where fixes apply to every sheet.

Rename, hide and alias

  • Rename a field: double-click it, or use the column menu on the Data Source page. cust_nm becomes Customer Name.
  • Hide fields nobody needs (right-click > Hide). A tidy Data pane helps every author.
  • Aliases change how a dimension member is displayed (Right-click a header > Edit Alias, or Default Properties > Aliases). "CA" can display as California without changing the data.

Split text into columns

When one column holds two facts, such as "Smith, Anna" or "Furniture-Chairs", use Split. Right-click the field in the Data pane (or the column on the Data Source page) and choose Transform > Split.

  • Split (automatic) guesses the separator.
  • Custom Split... lets you choose the separator and how many columns to create (first N, last N, or all).

Tableau creates new calculated fields such as Name - Split 1. In a calculated field you would write the same thing with SPLIT([Name], ",", 1), or with LEFT, RIGHT, MID and FIND for fixed patterns. Always TRIM the result.

TEXT
TRIM(SPLIT([Customer], ",", 1))                  // "Smith"
TRIM(SPLIT([Customer], ",", 2))                  // "Anna"
LEFT([Phone], FIND([Phone], "-") - 1)            // text before the first dash

Pivot wide data into tall data

A table with one column per month (Jan, Feb, Mar...) is hard to chart because months are separate fields. Pivot turns the columns into rows: one column of month names and one of values. On the Data Source page, select the columns, right-click and choose Pivot. Tableau creates Pivot Field Names and Pivot Field Values; rename them to Month and Sales.

Python Stand-in: split a name and pivot months (pandas)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

After pivoting, a single Sales measure and a Month dimension let you build one line chart instead of three separate measures.

Nulls and bad values

Nulls appear as a Null label or a small "n Null" indicator in the bottom-right of the view. Handle them deliberately:

TEXT
ZN([Discount])                 // zero if null
IFNULL([Region], "Unknown")    // replacement text if null
IF ISNULL([Ship Date]) THEN "Not shipped" ELSE "Shipped" END

Also check for duplicates (look at the distinct count of the key), impossible dates, and numbers stored as text (see Data types, dimensions and measures).

Data Interpreter and Prep

The Data Interpreter (a checkbox on the Data Source page for Excel, CSV and Google Sheets) removes header decorations, notes and totals, and can detect sub-tables. For heavier work (joining, aggregating, fuzzy grouping, scheduled flows) use Tableau Prep Builder, which produces a .hyper extract or published data source that your workbooks connect to.

Python Stand-in: ZN, IFNULL and a clean-up report

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Common mistakes

  • Blending when a relationship would do, then fighting blend limitations.
  • Forgetting the primary source. The first source you use in the view is primary; a blend with the wrong primary shows the wrong marks.
  • Filtering in the secondary source and expecting the primary to follow.
  • Cleaning in each worksheet instead of once on the Data Source page.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. In a data blend, which source decides which marks appear?

    The primary source drives the view. Secondary data is matched onto its marks, like a left join of aggregated results.
  2. 2. What icon marks a link field between two data sources?

    The orange chain shows which shared field links the secondary source to the primary.
  3. 3. What does Pivot do?

    Pivoting reshapes wide data into tall data so one measure and one dimension can drive a chart.
  4. 4. Which calculation replaces null discounts with zero?

    ZN returns zero when the value is null and the value itself otherwise.

Summary

  • A blend aggregates the secondary source at the link level and attaches it to the primary source's marks.
  • Prefer relationships or joins when data sources can be combined; use blending for separate sources and coarse-level data.
  • Clean once on the Data Source page: rename, hide, alias, split, pivot.
  • Handle nulls on purpose with ZN, IFNULL and ISNULL; use Prep for bigger jobs.

Next: Bar, line and area charts