Đại học Kinh tế Quốc dân – National Economics UniversityMICRO4ASIA
🏆 Arena
Sign inSign up
MICRO4ASIAThe six micro-credentials

Courses are delivered on the Limio learning platform. Limio

← Data Visualization in Business

You are viewing a public preview

Sign in to save your progress, do assignments, join discussions and get feedback.

Sign in
Module 2 — Data Connection and Preprocessing in Tableau

2.3 · Data Preprocessing with Tableau Prep

Print / Save as PDF

In this lesson

The Tableau Prep workflow; data profiling and quality assessment; handling missing values and duplicates; correcting data types; standardizing and replacing values; splitting fields; calculated fields; joins, unions, pivots, aggregation, output and flow management.

Learning outcomes

  • LO.3: Organize tabular data, identify fields and data types, set keys and relationships, and connect suitable data sources.
  • LO.4: Prepare, clean, standardize, transform and check data with Tableau Prep.
  • LO.5: Use Tableau Desktop and Tableau Public to connect data, organize fields and build visualizations.

The lecture slides for this lesson are below.

Practice for Module 2

Design tables and relationships for a small sales dataset; connect Excel and CSV sources in Tableau; use Tableau Prep to profile, clean, standardize, transform, combine, validate and export the data. (About 15 hours of independent study.)

Study notes

By the end of this lesson you can:

  • Describe the Tableau Prep Builder interface and how a flow is built from steps.
  • Profile a new dataset and identify the four common data quality issues.
  • Choose a suitable cleaning operation for each issue and justify it in business terms.
  • Choose between calculated fields, aggregate, pivot, join and union to transform and combine data.
  • Validate a cleaned dataset and export it in a suitable format.

Key idea: Data preparation is a documented, repeatable flow, not a series of one-off fixes. Look first, fix with business reasons, and validate the result before anyone builds a chart on it.

Tableau Prep and its interface

Tableau Prep supports data preprocessing through visual flows covering connection, cleaning, transformation and integration. Tableau Prep Builder is the desktop tool where you design flows using Clean, Join, Union, Pivot and Aggregate steps. Tableau Prep Conductor publishes, schedules, runs and monitors those flows on Tableau Server or Tableau Cloud.

The Prep Builder interface has four main areas:

  • Connections pane: the data sources you have connected.
  • Flow pane: the flow diagram, showing the sequence of steps from input to output.
  • Profile pane: one summary card per field with value distributions, missing values and other characteristics.
  • Data grid: the record-level data, so you can inspect individual rows.

Explore before you clean

After connecting, inspect five things: the number of records and fields, the field names and meanings, the data types, the level of detail (what entity or event each row represents) and the distinct values (common values, unusual values and missing values). The Profile pane makes many problems visible at once: a category field with eight spellings, a quantity histogram with negative values, or a date field with nulls.

Four common data quality issues

IssueWhat to checkPrinciple
Missing valuesNumber, proportion and cause of NULLsDo not automatically replace NULL with 0
Duplicate recordsRepeated rows based on relevant fields or business keysJudge duplicates at the table's level of detail
Outliers and noisy dataUnusual values and obvious errorsVerify validity before modifying or removing
Inconsistent dataFormats, units, encoding, category namesStandardise to one form

Every cleaning decision changes the numbers people will use, so it needs a business reason: replacing a missing profit with 0 claims the line broke even, which is not the same as "unknown".

Cleaning and standardisation operations

  • Managing fields: rename, remove, keep only, duplicate or hide fields.
  • Changing data types: convert to String, Number, Date, Boolean or a geographic role.
  • Filtering: keep records by category, numeric range, period or a logical condition.
  • Handling missing and anomalous values: keep, replace, label or remove NULLs and outliers, depending on business meaning.
  • Group and replace: combine equivalent spellings into one standard value. Prep can suggest groups by pronunciation, common characters or spelling, or you select values manually.
  • Split and custom split: divide a text field into several fields by a delimiter or rule.

Transformation and integration

  • Calculated fields: new fields built from existing fields, constants, operators and functions (for example IFNULL to fill a value that can be derived from other fields).
  • Aggregate: group rows and compute SUM, AVG, COUNT, MIN, MAX; this changes the level of detail.
  • Pivot: columns to rows (wide to long) or rows to columns.
  • Join: combine tables column-wise on common keys (inner, left, right, full outer).
  • Union: append rows from tables with compatible structures; Prep adds a field recording which table each row came from.

Validate, export and manage flows

Validation checks the number of records, structure, data types, duplicates, NULLs, business rules (for example Ship Date not before Order Date) and aggregated metrics such as total sales. Output writes the result as a Tableau extract (.hyper), CSV or Excel file, or publishes it. Save the flow (.tfl) and document it, so you can rerun it when the source is updated; Prep Conductor can schedule and monitor published flows.

Worked example

Plan, before clicking anything, the order of steps for preparing the raw orders of the course dataset, and justify each step.

  1. Union first. The orders are split across two sheets with the same structure; union them before cleaning so each fix is done once and duplicates across sheets are caught.
  2. Profile. Read every field card and write down all problems before fixing any of them, so that one fix does not hide another.
  3. Standardise text before grouping or de-duplicating. Trim spaces and fix capitalisation first; otherwise "South " and "South" count as different values and two copies of a row look different.
  4. Remove exact duplicates at the order-line level, and compare the row count before and after.
  5. Missing values with a rule. A missing Sales value can be derived from other fields of the same row (price, quantity, discount), so a calculation is justified. A missing Profit depends on costs that are not in the data, so it stays NULL and is reported.
  6. Separate, don't delete, suspicious rows. Rows that break business rules go to a separate review output with a flag, so nothing disappears silently.
  7. Join last. Join the cleaned orders to the cleaned customers and products only after their keys are standardised and unique.
  8. Validate and export. Compare record count and total sales with the expected values, then output a .hyper or CSV file.

Common mistakes

  • Replacing every NULL with 0. This turns "unknown" into a false value and biases averages.
  • Deleting outliers without checking. A very large order may be real; verify before removing.
  • Joining before cleaning keys. Unstandardised or duplicated keys lose matches or multiply rows.
  • Trusting the profile of a sample. For large data Prep may profile a sample of rows; the output still processes all rows, so validate the final output.

Summary

  • Prep Builder builds flows visually; Prep Conductor schedules and monitors them on Server or Cloud.
  • Explore records, fields, types, level of detail and values before cleaning.
  • The four common issues are missing values, duplicates, outliers and inconsistent data.
  • Every fix needs a business reason; keep doubtful rows for review rather than deleting them.
  • Validate counts, keys, NULLs, types, ranges and totals, then export and save the flow.

Flashcards · Data Preprocessing with Tableau Prep

10 words
WordMeaningExample
  • Tableau Prep Builder

    A desktop tool for designing visual flows that connect, clean, transform and combine data.

    A flow: Input → Union → Clean → Join → Output.

  • Profile pane

    The Prep area showing one summary card per field with value distributions and missing values.

    Spot eight spellings of Ship Mode at a glance.

  • Clean step

    A Prep step for field-level changes: rename, retype, filter, group values, split and calculate.

    Trim spaces in Customer Name.

  • Group Values

    Combines different spellings of the same value into one standard value.

    '2nd Class' and 'second class' become Second Class.

  • Custom Split

    Splits a text field into several fields using a chosen separator.

    'Furniture - Chairs' split on ' - '.

  • IFNULL

    Returns the first expression if it is not NULL, otherwise the second.

    IFNULL([Sales], [Unit Price] * [Quantity] * (1 - [Discount]))

  • Aggregate step

    Groups rows and computes SUM, AVG, COUNT, MIN or MAX, changing the level of detail.

    Sales summed by Region and month.

  • Pivot (columns to rows)

    Turns repeated columns into rows, converting wide data to long data.

    Month columns become Month and Sales Target.

  • Output step

    Writes the flow's result to a .hyper, CSV or Excel file, or publishes it.

    Output cleaned orders to Sales_Clean.hyper.

  • Data validation

    Checking counts, types, duplicates, NULLs, business rules and totals after processing.

    Ship Date is never earlier than Order Date.

Practice in Tableau Prep — Lab 2B: Clean the raw sales data

Open Tableau Prep Builder and connect to Sales_Data_Raw.xlsx (attached to the Course Overview lesson). The file contains deliberate data-quality problems, exactly the four types covered in this lesson: missing values, duplicates, outliers and inconsistent data.

  1. Union. Add Orders_Raw and Orders_Raw_2025 and union them. Check: 4,785 rows.
  2. Profile. Add a Clean step and read the Profile pane. List every problem you see before fixing anything.
  3. Duplicates. Remove rows that are exact copies of another row (the Remove Duplicates option in a Clean step, or an Aggregate step grouped by every field). Check: 15 duplicates removed, 4,770 rows left.
  4. Inconsistent labels. In Ship Mode, use Group Values so that variants such as second class, 2nd Class, Std Class and STANDARD CLASS become the four standard modes. Check: exactly 4 values.
  5. Missing values. 6 rows have no Sales. Recalculate them with a calculated field: IFNULL([Sales], [Unit Price] * [Quantity] * (1 - [Discount])). 4 rows have no Profit: keep them, but explain why you should not invent a profit value.
  6. Invalid and extreme values. Create a field Quality Flag that marks rows with Quantity ≤ 0, Discount > 1, an Order Date that is not a valid date, a Ship Date that is missing or earlier than the Order Date, or a Unit Price far above the product's list price. Filter the flagged rows into a separate output for review instead of deleting them silently.
  7. Customers. Connect Customer_Raw: trim spaces and fix capitalisation in Customer Name, group the Region variants (north, N., Northern, South …) into North, Central and South, and remove the duplicate customers. Check: 240 customers, 3 regions.
  8. Products. In Product_Raw, use Custom Split on Category / Sub-Category with the separator - to create Category and Sub-Category.
  9. Targets. Targets_Wide has one column per month. Use a Pivot (columns to rows) to turn it into Region × Month × Sales Target. Check: 36 rows.
  10. Output. Join the cleaned orders with customers and products and output the result as a .hyper or .csv file. Compare its row count and total sales with the clean file Sales_Data.xlsx: they should match except for the rows you flagged.