Data Types — Set data types correctly (string, number, date, datetime) and fix parsing issues - Tableau

SECTION 1

Historical Context & Motivation

The concept of data typing is foundational across computing—from early programming languages enforcing strict type declarations to modern analytics platforms that must infer types from heterogeneous data sources. In the context of visual analytics, the distinction between a string "2024-01-15" and a proper date object determines whether Tableau can place that value on a temporal axis, compute date differences, or aggregate by quarter. When Tableau was first released in 2003, it inherited data type challenges from the broader ecosystem of SQL databases, flat files, and spreadsheets, each of which encodes types differently. As the volume and variety of data sources grew—CSV exports, JSON APIs, cloud data warehouses—the problem of type inference and parsing became increasingly central to the data preparation workflow.

1970s • Relational Model & SQL Types

Codd's relational model introduced formal column types (INTEGER, VARCHAR, DATE) in databases, establishing the foundation for strongly-typed tabular data that modern BI tools inherit.

2003 • Tableau's First Release

Tableau launched with automatic type inference from database connections and flat files, abstracting SQL types into a simplified set: String, Number (Integer/Decimal), Date, Date & Time, and Boolean.

2010s • Rise of Messy Data Sources

The proliferation of CSV exports, web scraping, and API-driven data introduced widespread parsing ambiguities—mixed formats, locale-specific date strings, and numbers stored as text with currency symbols or thousands separators.

2018 • Tableau Prep Builder

Tableau released Prep Builder, offering a dedicated visual interface for data cleaning and type correction before analysis, acknowledging that type issues were a primary barrier to effective visualization.

2020s • Automated Type Detection & AI-Assisted Prep

Modern Tableau versions employ improved heuristics and allow users to define custom date parse formats, reflecting the industry-wide push toward self-service analytics with minimal data engineering overhead.

The central question this lesson addresses is deceptively simple: how does Tableau decide what type a field is, when does it get it wrong, and what are the consequences of mistyped data? A column of ZIP codes read as integers silently loses leading zeros; a revenue field parsed as a string prevents summation; a datetime column misinterpreted due to locale differences shifts every record by hours or days. Understanding the type system and mastering the tools for correction is essential for any rigorous analytical workflow in Tableau.

SECTION 2

Core Principles & Definitions

Tableau organizes every field in a data source into one of a small set of data types, each of which governs what operations are available, how the field can be placed on shelves, and how Tableau renders values in the view. These types map conceptually to type systems you've encountered in languages like Java or Python, but Tableau adds a layer of abstraction because it must also classify fields as dimensions (qualitative) or measures (quantitative)—a semantic classification that interacts with, but is distinct from, the underlying data type. A field's data type determines its domain of valid values and allowable transformations, while its role as a dimension or measure determines its default aggregation behavior.

String (Abc)

Represents textual data—names, categories, identifiers. Strings support concatenation, CONTAINS(), and REPLACE() functions but cannot be aggregated numerically. Tableau displays the Abc icon for string fields. Common pitfall: numeric IDs (e.g., ZIP codes) incorrectly parsed as integers.

Number (Integer & Decimal)

Represents quantitative values. Integers (# icon) hold whole numbers; decimals (#.# icon) hold floating-point values. Numbers support SUM, AVG, MIN, MAX, and arithmetic operators. Parsing fails when values contain currency symbols ($), thousands separators (commas), or percent signs.

Date

Stores calendar dates without time components—year, month, day. Enables date-part extraction (YEAR(), MONTH(), DAY()), date arithmetic (DATEDIFF, DATEADD), and temporal hierarchies. Tableau's calendar icon marks date fields. Locale-dependent formats (MM/DD vs DD/MM) are the primary source of parsing errors.

Date & Time (Datetime)

Extends Date with hours, minutes, seconds, and optional fractional seconds. Critical for event-level analysis (log files, transactions). Parsing issues arise from time zone encoding, 12-hour vs 24-hour formats, and ISO 8601 vs custom timestamp strings.

Boolean

Holds TRUE or FALSE values. Often derived from calculated fields (e.g., [Sales] > 1000). Useful for filtering and conditional logic. Source data may encode booleans as 0/1, Y/N, or True/False strings, requiring explicit type casting in Tableau.

✦

KEY TAKEAWAY

Think of data types as the contract between your data and Tableau's engine. Just as a C++ compiler refuses to compile when you pass a string to a function expecting an int, Tableau will silently fail—dropping values to Null or disabling aggregation—when a field's declared type doesn't match its actual content. The difference in an analytics context is that Tableau often guesses the type rather than requiring you to declare it, which means incorrect inferences go unnoticed until you see unexpected Nulls or broken axes. Proactively auditing and correcting types is analogous to writing type annotations in Python: it's optional but dramatically reduces runtime errors.

SECTION 3

Visual Explanation — Tableau's Type Inference Pipeline

The diagram traces data from source to assigned type, highlighting the three most common parsing failure modes (numbers as strings, date ambiguity, and identifiers as numbers) and the resolution strategies available in Tableau Desktop and Tableau Prep.

The pipeline illustrated above reflects Tableau's two-phase approach to type assignment. When connecting to a database, Tableau leverages the existing schema metadata—column types defined in the DDL—and maps them directly to Tableau's internal type system. A SQL INTEGER becomes a Tableau Number (whole), a SQL TIMESTAMP becomes Date & Time, and so forth. However, when connecting to flat files like CSVs—which carry no schema—Tableau must sample rows and apply heuristic pattern matching to infer types. This sampling-based inference is inherently fragile: a column of mostly numeric values with a single text entry (e.g., "N/A") may be classified as a string, and a date column whose format doesn't match Tableau's expected locale pattern may remain a string. Understanding where your data falls in this pipeline tells you where to intervene.

SECTION 4

How Type Conversion Works in Tableau

Tableau provides both implicit and explicit type conversion mechanisms. Implicit conversion occurs when you right-click a field in the Data pane and select "Change Data Type." Tableau attempts to reinterpret the existing string representation according to the target type's expected format. Explicit conversion involves writing calculated fields using Tableau's type-casting functions, which give you fine-grained control over the parsing logic. Understanding these functions is essential because implicit conversion often fails silently—producing Null values—when the source data doesn't conform to Tableau's default format expectations.

Key Type-Casting Functions

STRING TO NUMBER

INT(REPLACE(REPLACE([Revenue], "\$", ""), ",", ""))

First strip non-numeric characters (currency symbols, thousands separators) using nested REPLACE() calls, then cast the cleaned string to an integer with INT(). Use FLOAT() for decimal precision.

STRING TO DATE (DATEPARSE)

DATEPARSE("dd/MM/yyyy", [Date_String])

The DATEPARSE() function takes a format string using Java's SimpleDateFormat tokens. "dd" = day (01–31), "MM" = month (01–12), "yyyy" = four-digit year, "HH" = 24-hour hour, "mm" = minute, "ss" = second. Available only with certain extract and file-based connections.

DATE CONSTRUCTION FROM PARTS

MAKEDATE([Year_Field], [Month_Field], [Day_Field])

When date components are stored in separate integer columns, MAKEDATE() constructs a proper Date value. The analogous MAKEDATETIME() accepts a Date and a Time argument to produce a Datetime.

NUMBER TO STRING

STR([ZIP_Code])

Converts a numeric field to its string representation. Note: leading zeros lost during numeric parsing cannot be recovered by casting back to string—the fix must happen at the data source. Use IF LEN(STR([ZIP])) < 5 THEN RIGHT("00000" + STR([ZIP]), 5) ELSE STR([ZIP]) END to re-pad.

⚠

DATEPARSE Availability

The DATEPARSE() function is not available for all connection types. It works with file-based data sources (CSV, Excel, JSON) and Tableau extracts, but not with live connections to most SQL databases. For live DB connections, use database-native date parsing (e.g., TO_DATE() in Oracle, STR_TO_DATE() in MySQL) via Tableau's pass-through SQL or custom SQL queries, or create an extract to unlock DATEPARSE.

SECTION 5

Data Type Classification & Parsing Issue Taxonomy

Parsing issues in Tableau can be systematically categorized by which type conversion is failing and why. The taxonomy below maps common data quality problems to their root causes and recommended fixes. Understanding this classification allows you to diagnose type issues efficiently rather than relying on trial and error—a critical skill when working with unfamiliar datasets under time pressure.

This decision tree guides you through diagnosing the most common type parsing failures in Tableau. Start from the top: identify whether you expected a number or a date, check whether the field is showing as the wrong type, and follow the branches to the appropriate fix. The yellow bar reminds you that Null values appearing after a type change are the primary signal that parsing has failed.

Symptom	Root Cause	Fix
Numeric field shows Abc icon	Values contain $, %, commas, or mixed text entries ("N/A")	REPLACE() to strip symbols, then INT() or FLOAT(); handle text entries with IF/ELSE returning Null
Date field shows Abc icon	Date format not recognized by Tableau's locale settings (e.g., DD-Mon-YYYY)	DATEPARSE() with correct format string, or pre-process in Tableau Prep
Dates are off by months	MM/DD vs DD/MM locale mismatch; Tableau parsed day as month	DATEPARSE() specifying correct order; verify with known reference dates
ZIP codes missing leading zeros	Field parsed as integer, dropping 0-prefix	Cast to String; pad with RIGHT("00000" + STR([ZIP]),5)
Null values after type change	Some rows have values that cannot be parsed to the target type	Inspect Null rows in data preview; clean or exclude; use IFNULL() for defaults

Common parsing issues: symptom, cause, and fix

SECTION 6

Worked Example — Cleaning a Messy Sales Dataset

Consider a CSV file exported from a legacy ERP system containing sales transactions. The file has four problematic columns: Order_Date (stored as "15-Jan-2024"), Revenue (stored as "$12,340.50"), Store_ID (stored as numeric 00142, which CSV dropped to 142), and Timestamp (stored as "2024-01-15 2:30 PM"). When Tableau connects to this CSV, it infers Order_Date as String, Revenue as String, Store_ID as Number (Integer), and Timestamp as String. Our goal is to correct all four.

Fixing Four Type Issues in a Sales Dataset

1
Step 1 — Diagnose the Issues

Open the Data Source page and examine the type icons. Order_Date shows Abc (string). Revenue shows Abc (string). Store_ID shows # (integer)—this is semantically wrong since Store_ID is a categorical identifier. Timestamp shows Abc (string). Right-clicking Order_Date and changing to Date produces Null values because "15-Jan-2024" doesn't match Tableau's default date pattern.

All four fields require manual intervention; implicit conversion fails for three of them.
2
Step 2 — Fix Order_Date with DATEPARSE

Create a calculated field named Order_Date_Clean:DATEPARSE("dd-MMM-yyyy", [Order_Date]). Here, "dd" matches the two-digit day, "MMM" matches the three-letter month abbreviation (Jan, Feb, etc.), and "yyyy" matches the four-digit year. Verify by placing the new field on Rows and checking that dates render correctly on a timeline.

Order_Date_Clean is now a proper Date field; Tableau shows the calendar icon.
3
Step 3 — Fix Revenue with REPLACE and FLOAT

Create a calculated field named Revenue_Clean:FLOAT(REPLACE(REPLACE([Revenue], "$", ""), ",", "")). The inner REPLACE removes the dollar sign, the outer REPLACE removes thousands-separator commas, and FLOAT converts the resulting numeric string "12340.50" to a decimal number. Drag Revenue_Clean to Rows with SUM aggregation to verify it sums correctly.

Revenue_Clean = 12340.50 (Number, Decimal)
4
Step 4 — Fix Store_ID by Casting to String and Padding

Create a calculated field named Store_ID_Clean:RIGHT("00000" + STR([Store_ID]), 5). This converts the integer 142 to the string "142", prepends five zeros to get "00000142", then takes the rightmost 5 characters to produce "00142". The field is now a string and correctly retains the leading zeros. Change its role to Dimension if Tableau defaulted it to Measure.

Store_ID_Clean = "00142" (String, Dimension)
5
Step 5 — Fix Timestamp with DATEPARSE (Datetime)

Create a calculated field named Timestamp_Clean:DATEPARSE("yyyy-MM-dd h:mm a", [Timestamp]). Here, "h" matches 12-hour format without leading zero, "mm" matches minutes, and "a" matches the AM/PM marker. The result is a proper Datetime field. Verify by checking that the time component renders correctly when you set the date part to Exact Date.

Timestamp_Clean = 2024-01-15 14:30:00 (Date & Time)

SECTION 7

Strengths & Limitations of Tableau's Type Handling

Aspect	Strengths	Limitations
Automatic inference	Works seamlessly for clean, well-typed database sources; zero configuration needed for standard schemas	Heuristic-based for flat files; easily confused by mixed-type columns or non-standard formats
UI-based type change	One-click type change in Data pane; accessible to non-technical users	Fails silently (produces Nulls) when values can't parse; no error message or parse failure count
DATEPARSE()	Supports Java SimpleDateFormat for precise control over non-standard date strings	Not available for live database connections; only works with extracts and file-based sources
Calculated fields for casting	Full flexibility: REPLACE, IF/ELSE, REGEX (in some contexts) for complex transformations	Requires knowledge of Tableau's calculation syntax; clutters the field list if many fixes needed
Tableau Prep integration	Visual data cleaning pipeline; handles type changes, splits, and joins before Desktop analysis	Separate tool; requires additional licensing in some editions; adds workflow complexity

Comparing Tableau's type handling capabilities

✦

KEY TAKEAWAY

Tableau's approach to data types mirrors the tradeoff between dynamically-typed and statically-typed languages. Like Python, Tableau infers types at runtime (connection time), which accelerates the initial workflow but defers errors to the analysis phase. The best practice—analogous to adding type hints in Python or writing schema definitions in a data pipeline—is to always audit field types immediately after connecting to a new data source. Treat the Data Source page as your 'compile step': catch type errors there before they propagate into dashboards and corrupt downstream calculations.

SECTION 8

Connection to Advanced Data Engineering Concepts

The type management skills you develop in Tableau translate directly to broader data engineering practices. In production data pipelines, type enforcement is handled by schema-on-read versus schema-on-write paradigms. Traditional relational databases enforce schema-on-write: types are declared at table creation and the database rejects non-conforming inserts. Data lakes and flat file workflows employ schema-on-read: the raw data is stored as-is, and type interpretation happens at query time—exactly what Tableau does when connecting to a CSV. Understanding this distinction clarifies why type issues are endemic to self-service analytics and why upstream data quality processes (ETL/ELT pipelines, data contracts, schema registries) are critical in production environments.

Concept	In Tableau	In Data Engineering
Type inference	Automatic on CSV/Excel connection based on row sampling	Schema inference in Spark (inferSchema=true), pandas (read_csv dtype), or BigQuery auto-detect
Explicit casting	INT(), FLOAT(), STR(), DATEPARSE() calculated fields	CAST() in SQL, .astype() in pandas, Schema objects in Avro/Protobuf
Null handling on parse failure	Silent Null insertion; IFNULL() or ZN() for defaults	Configurable: raise exception (fail fast), coerce to Null, or log to dead-letter queue
Date format specification	Java SimpleDateFormat tokens in DATEPARSE()	strftime/strptime in Python, TO_DATE format masks in SQL, ISO 8601 as universal standard
Schema validation	Manual audit of Data Source page; no automated validation	Great Expectations, dbt tests, JSON Schema, or database CHECK constraints

Mapping Tableau type concepts to data engineering equivalents

As you advance into roles involving data modeling or analytics engineering, the principles you've learned here—explicit type declaration, format-aware parsing, and proactive Null detection—form the foundation of data contracts and schema evolution strategies that ensure data quality at scale. Tableau Prep's type-handling capabilities are a visual introduction to the transformations that tools like dbt, Apache Spark, and Airflow perform programmatically in production ETL/ELT pipelines.

SECTION 9

Practice Problems

Question 1: CONCEPTUAL

Explain why Tableau might infer a column of US ZIP codes as an integer data type when connecting to a CSV file. What data quality issue does this cause, and how does it differ from connecting to the same data in a PostgreSQL database where the column is defined as VARCHAR(5)?

Show answer

CSV files carry no schema metadata, so Tableau samples rows and applies heuristic pattern matching. If all sampled ZIP codes happen to be numeric strings (e.g., "90210", "10001"), Tableau infers the column as an integer. This causes ZIP codes with leading zeros (e.g., "01234" for parts of Massachusetts) to lose their prefix and become 1234, which is both semantically incorrect and breaks any geographic lookup. When connecting to PostgreSQL, Tableau reads the column's DDL-defined type (VARCHAR(5)) directly from the database catalog and maps it to String, preserving leading zeros. This illustrates the fundamental difference between schema-on-read (CSV) and schema-on-write (relational DB) paradigms for type assignment.

Question 2: BASIC CALCULATION

A CSV column called Price contains values formatted as "€1.234,56" (European format: period as thousands separator, comma as decimal separator). Write a Tableau calculated field that converts this string to a proper decimal number.

Show answer

The calculated field should first remove the euro sign, then handle the European number format by removing the period (thousands separator) and replacing the comma (decimal separator) with a period: FLOAT(REPLACE(REPLACE(REPLACE([Price], "€", ""), ".", ""), ",", ".")). The innermost REPLACE strips the currency symbol. The next REPLACE removes period-based thousands separators ("1.234,56" → "1234,56"). The outer REPLACE converts the comma decimal separator to a period ("1234,56" → "1234.56"). Finally, FLOAT() casts the clean string to a decimal number. This yields 1234.56. The order of REPLACE operations matters—if you replaced commas first, you'd convert the decimal comma to a period and then the thousands period would remain, producing an invalid number.

Question 3: INTERMEDIATE

You connect to a CSV containing event logs. The event_time column stores timestamps in the format "15/01/2024 02:30:45 PM" (DD/MM/YYYY, 12-hour clock with AM/PM). Tableau infers this as a String. Write a DATEPARSE expression to correctly convert it, and explain what would go wrong if you simply right-clicked and changed the type to Date & Time without using DATEPARSE.

Show answer

The correct DATEPARSE expression is: DATEPARSE("dd/MM/yyyy hh:mm:ss a", [event_time]). Here, "dd" = two-digit day, "MM" = two-digit month, "yyyy" = four-digit year, "hh" = 12-hour hour with leading zero, "mm" = minutes, "ss" = seconds, and "a" = AM/PM marker. If you instead right-click and change to Date & Time, Tableau's default parser (which typically expects US locale MM/DD/YYYY) would interpret "15/01/2024" by trying to read 15 as a month. Since month 15 doesn't exist, the parse fails and produces Null for every row. Even for dates where DD ≤ 12, like "05/01/2024", Tableau would silently swap month and day—interpreting it as May 1st instead of January 5th. This is the classic locale mismatch problem: the data is DD/MM but Tableau assumes MM/DD, producing either Nulls or silently incorrect dates.

Question 4: APPLIED

You are building a Tableau dashboard for a multinational retailer. The source data comes from three regional systems: US (dates as MM/DD/YYYY, currency as $1,234.56), Germany (dates as DD.MM.YYYY, currency as 1.234,56€), and Japan (dates as YYYY年MM月DD日, currency as ¥1,234). Describe a comprehensive data preparation strategy using Tableau Prep or calculated fields to unify all three formats into consistent types.

Show answer

The optimal approach uses Tableau Prep Builder to create a unified cleaning pipeline. First, add a Union step to combine all three regional data sources. Add a region field identifying each source. Then create branching cleaning logic: for dates, use three conditional DATEPARSE expressions—US: DATEPARSE("MM/dd/yyyy", [date_str]), Germany: DATEPARSE("dd.MM.yyyy", [date_str]), Japan: DATEPARSE("yyyy年MM月dd日", [date_str])—wrapped in a CASE statement on [region]. For currency, strip region-specific symbols and separators: US requires removing $ and commas; Germany requires removing € and periods (thousands), then replacing comma with period (decimal); Japan requires removing ¥ and commas. Each is a nested REPLACE chain followed by FLOAT(). In Tableau Prep, these transformations are defined as calculated fields in a Clean step. Output the flow as a .hyper extract with properly typed Date and Number columns. Alternatively, in Desktop, create wrapper calculated fields using IF [Region] = 'US' THEN ... ELSEIF [Region] = 'DE' THEN ... logic. The Prep approach is superior because it runs once and produces clean data, whereas Desktop calculated fields execute on every query.

Question 5: CRITICAL THINKING

Tableau silently converts unparseable values to Null during type changes. Propose a systematic methodology—using only Tableau Desktop features (no Prep)—to detect, quantify, and remediate all parsing failures in a dataset with 50 columns and 2 million rows. Discuss the tradeoffs between fixing types at the source versus fixing them in Tableau, and argue for which approach is more appropriate in a production analytics environment.

Show answer

A systematic methodology in Tableau Desktop would proceed in three phases. Phase 1 (Detection): For each field, create a calculated field Parse_Check_[FieldName] that attempts the type conversion and compares the Null count before and after—e.g., IF ISNULL(INT([field])) AND NOT ISNULL([field]) THEN 1 ELSE 0 END counts rows where the original value exists but the cast fails. Build a diagnostic dashboard showing SUM of parse failures per column. Phase 2 (Quantification): Calculate the parse failure rate as a percentage: SUM(parse failures) / COUNTD(non-null original values). Set threshold rules—e.g., <1% failure rate is acceptable for conversion with Null fallback; 1–10% requires investigation; >10% indicates a fundamental format mismatch. Phase 3 (Remediation): For each failing column, inspect sample failing values using a filter on the parse check field, identify the pattern (symbols, locale, mixed types), and write targeted REPLACE/DATEPARSE expressions. Regarding the source-vs-Tableau tradeoff: fixing at the source (database ETL, data contracts, schema enforcement) is superior for production because it ensures all downstream consumers—not just Tableau—receive correctly typed data, the fix runs once rather than on every query, and it enables automated testing (e.g., dbt tests, Great Expectations). Fixing in Tableau is appropriate for ad-hoc analysis or when you lack write access to the source. In production, Tableau should be a consumer of well-typed data, not the type enforcement layer.

SUMMARY

Lesson Summary

Summary

Tableau's data type system classifies every field as String, Number (Integer or Decimal), Date, Date & Time, or Boolean. When connecting to schema-rich sources like relational databases, type mapping is reliable. When connecting to schema-less sources like CSV files, Tableau employs heuristic type inference based on row sampling, which frequently misclassifies fields—especially dates in non-US formats, numbers with embedded currency or locale-specific separators, and categorical identifiers that happen to look numeric.

Fixing parsing issues requires a combination of the right-click type change for simple cases and calculated fields using DATEPARSE(), INT() / FLOAT(), REPLACE(), and STR() for complex transformations. The critical diagnostic signal is unexpected Null values after a type change, which indicate rows where parsing failed silently. Always audit types on the Data Source page immediately after connecting, verify date correctness against known reference records, and prefer upstream schema enforcement in production environments to keep Tableau focused on analysis rather than data cleaning.