Study guide
3.2 In-Desktop Data Cleaning: Splits, Data Interpreter & Source Replacement
Salesforce Certified Tableau Data Analyst Exam
Track your progress as you read
Finished sections get a check mark.
1 Chapter 1: Connecting to Data & The Logical Data Model
1.1 Data Connections: Live Connections vs. Hyper Extracts 1.2 The Tableau Data Model: Relationships vs. Physical Joins 1.3 Combining Data: Data Blending vs. Cross-Database Joins
2 Chapter 2: Tableau Prep Builder & Data Wrangling
2.1 Tableau Prep Architecture & Data Cleaning 2.2 Reshaping Data in Prep: Pivots, Splits & Wildcard Unions 2.3 Joins, Aggregations & Output Destinations in Prep
3 Chapter 3: Data Quality, Metadata & Field Configuration
3.1 Data Types, Roles, Organization & Default Properties 3.2 In-Desktop Data Cleaning: Splits, Data Interpreter & Source Replacement 3.3 Managing Slowly Changing Dimensions & Data Quality Warnings
4 Chapter 4: Tableau Fundamentals & Calculations
4.1 Dimensions vs. Measures & Discrete vs. Continuous 4.2 Mastering Dates: Discrete Date Parts vs. Continuous Date Truncations 4.3 Calculations: Logic, Strings, Nulls & Type Conversion 4.4 Aggregation Rules, Granularity & Dealing with the Asterisk (ATTR)
5 Chapter 5: Level of Detail (LOD) Expressions
5.1 Level of Detail Architecture & Scoping Syntax 5.2 FIXED LOD Expressions: Scoping & Baseline Benchmarks 5.3 INCLUDE & EXCLUDE LOD Expressions: Dynamic Granularity 5.4 Real-World LOD Patterns: Cohorts, First/Last Dates & Binning
6 Chapter 6: Table Calculations & Directionality
6.1 Table Calculation Mechanics: Addressing vs. Partitioning 6.2 Essential Table Calculations: Running, Window & Percent of Total 6.3 Navigation & Ranking Functions: LOOKUP, INDEX & RANK 6.4 Secondary & Nested Table Calculations: Multi-Level Scoping
7 Chapter 7: Statistical Analytics, Filtering & Order of Operations
7.1 The Tableau Order of Operations: From Extract to Display Filters 7.2 Sets, Set Actions & Combined Sets vs. Groups 7.3 Parameters, Parameter Actions & Dynamic What-If Analysis 7.4 Analytics Pane: Totals, References, Trends & Forecasts
8 Chapter 8: Chart Types & Comparative Visualizations
8.1 Comparison Visualizations: Bar Charts, Highlight Tables & Bullet Graphs 8.2 Part-to-Whole & Correlation Charts: Treemaps, Scatter Plots & Bubbles 8.3 Trend & Distribution Visualizations: Dual-Axis, Combos & Histograms
9 Chapter 9: Advanced Visualizations & Spatial Analysis
9.1 Specialized Visualizations: Waterfall, Gantt, Sparklines & Bump Charts 9.2 Spatial Data & Mapping: Geographic Layers & Spatial Functions 9.3 Visual Polish: Custom Palettes, Annotations & Viz in Tooltip
10 Chapter 10: Dashboard Design & Interactive Actions
10.1 Dashboard Layout, Devices & Visibility 10.2 Filter, Highlight & URL Actions 10.3 Advanced Interactivity: Parameter, Set & Navigation Actions 10.4 Stories & Visual Storytelling: Sequencing Analytical Narratives
11 Chapter 11: Tableau Server & Cloud: Architecture & Publishing
11.1 Tableau Server & Cloud: Sites, Projects, Roles & Permissions 11.2 Publishing Workbooks, Data Sources & Flows
12 Chapter 12: Data Governance, Security & Operations
12.1 Certification, Data Quality, Lineage & Permissions 12.2 Row-Level Security, Schedules, Subscriptions & Alerts
3.2 In-Desktop Data Cleaning: Splits, Data Interpreter & Source Replacement
Key Takeaways Explain This Topic AI
Data Interpreter parses Excel, CSV, text, and Google Sheets to remove extraneous multi-row headers, title blocks, merged cells, and trailing footnote summaries.
Quick Split automatically detects common delimiters across an entire string to split all segments, whereas Custom Split allows explicit delimiter definition and selection of First N, Last N, or All segments.
Aliases modify member display labels for discrete dimensions without changing underlying warehouse data, but aliases cannot be created on continuous measures, dates, or Published Data Sources in Desktop.
Replacing a data source transfers all worksheet shelves, calculated fields, and dashboard actions from the old connection to the new connection based on exact field name matching.
Broken calculated fields or missing pills resulting from schema changes (flagged with red exclamation marks) are resolved globally using the 'Replace References...' command.
Last updated: September 2026
3.2 In-Desktop Data Cleaning: Splits, Data Interpreter & Source Replacement
While enterprise data preparation often occurs upstream in data warehouses or Tableau Prep Builder, analysts frequently need to perform immediate data cleaning directly inside Tableau Desktop . Whether dealing with irregularly formatted human-designed spreadsheets, extracting embedded key-value pairs from concatenated string columns, or migrating a complex workbook from an initial Excel prototype to a governed cloud data warehouse, Tableau Desktop provides powerful native tools to clean, transform, and remap data models.
Data Interpreter: Automated Spreadsheet Normalization
Spreadsheets created in Microsoft Excel or Google Sheets are commonly structured for human consumption rather than computational analysis. They often include multi-line title banners, organizational metadata, merged cell headers, blank spacer rows, and trailing footnote summaries (such as quarterly totals or average calculations). When Tableau connects to such a sheet without intervention, it frequently misinterprets row 1 titles as column headers and generates field names like F1 , F2 , F3 .
How Data Interpreter Works
The Data Interpreter is a built-in heuristic engine in Tableau Desktop designed specifically for tabular files (Excel, .csv , .tsv , .txt , and Google Sheets). When enabled on the Data Source page:
Header Detection : It scans past leading title blocks, logos, and blank rows to identify the true rectangular data grid.
Merged Header Resolution : It fills in merged column headers so that sub-headers receive their full hierarchical context.
Footer Stripping : It identifies and removes trailing summary rows (e.g., Total , Grand Total , Average ) to prevent duplicate counting in analytical aggregations.
Data Type Normalization : It establishes clean column vectors with uniform data types. [Raw Excel Sheet] [With Data Interpreter Enabled]
- Row 1: Q3 Regional Sales Report (Title) --> (Stripped)
- Row 2: Confidential - Internal Only --> (Stripped)
- Row 3: Region | Rep Name | Sales --> Assigned as Column Headers
- Row 4: West | Alice | 15,000 --> Row 1 Data Record
- Row 5: East | Bob | 22,000 --> Row 2 Data Record
- Row 6: Total | 2 Reps | 37,000 --> (Stripped Summary Footnote)
- Reviewing the Results
After checking Use Data Interpreter , a notification appears with a clickable link: "Review the results" . Clicking this link opens a temporary Excel workbook containing annotated copies of each sheet. In this review workbook:
Green highlights : Cells recognized and imported as data records.
Red highlights : Cells recognized as column headers.
Patterned / greyed-out cells : Cells identified as titles, notes, or footers and excluded from the data model.
Exam Tip: Data Interpreter is supported only for flat files (Excel, text/CSV, Google Sheets). It is not available for relational databases (e.g., Snowflake, SQL Server, Oracle, PostgreSQL) or spatial files.
String Parsing: Quick Split vs. Custom Split
Raw datasets often combine multiple distinct analytical attributes into a single delimited string (e.g., an order transaction ID structured as CA-2026-152156 representing Country-Year-OrderID , or an email field first.last@department.company.com ). Tableau Desktop provides two native mechanisms to parse these fields on the Data Source page or the Data pane.
Quick Split
Mechanics : Right-click the string field and select Transform > Quick Split .
Behavior : Tableau automatically scans the column, identifies the primary recurring delimiter (such as a hyphen, space, comma, or slash), and splits the field into multiple new calculated fields across all detected occurrences.
Limitation : The user cannot choose the delimiter or specify how many columns to produce. It is an all-or-nothing automatic operation.
Custom Split
Mechanics : Right-click the string field and select Transform > Custom Split... .
Configuration Options :
Use the separator : Define any explicit string or character sequence (e.g., - , | , :: , @ ).
Split off : Choose from three behaviors:
First N columns : Extracts the specified number of segments starting from the left.
Last N columns : Extracts the specified number of segments starting from the right (ideal for paths, emails, or URLs where the final token is the target).
All : Extracts all available segments separated by the specified delimiter.
- | Feature | Quick Split | Custom Split |
- |---|---|---||
- | Delimiter Selection | Automatic heuristic detection | User-defined custom character/string |
- | Column Extraction Scope | Always splits all occurrences | User specifies First N, Last N, or All |
- | Underlying Formula | Generates SPLIT() calculations | Generates SPLIT() calculations |
- | Editable Calculation | Yes (can edit formula in Data pane) | Yes (can edit formula in Data pane) |
- The Generated Formula: SPLIT()
Both split methods create native calculated fields. Opening the calculated field reveals Tableau's underlying function syntax: TRIM( SPLIT( [Order_ID], "-", 1 ) )
The SPLIT(string, delimiter, token_number) function takes a negative token number when splitting from the right (e.g., -1 retrieves the last segment).
Aliases: Managing Member Names and Server Restrictions
Aliases allow authors to rename individual member values of a discrete dimension without altering the underlying data in the source database or writing an IF-THEN calculation. For example, converting country codes "US" to "United States" or status flags "P" to "Pending Approval" .
Creating and Editing Aliases
In the Data pane, right-click a discrete dimension and select Aliases... .
In the Aliases dialog, click the Value (Alias) column next to any member and type the replacement display label.
Click Clear Aliases at any time to restore original database values.
Critical Governance Limitations
Tableau enforces strict boundary rules regarding aliases:
Measures Cannot Have Aliases : Aliases can only be assigned to discrete dimensions. Continuous measures, quantitative numbers, and date fields do not support aliases.
Published Data Source Restriction : Once a data source is published to Tableau Server or Tableau Cloud, aliases cannot be created or edited in downstream workbooks using Tableau Desktop. The Aliases... menu option will be disabled or absent.
Workarounds for Published Sources : If display names must be altered on a published source, the author must either:
Edit the aliases in the data source before publishing.
Create a calculated field using CASE or IF-THEN logic (e.g., CASE [Code] WHEN 'US' THEN 'United States' ELSE [Code] END ).
Create an ad-hoc Group combining or renaming the members.
Replacing Data Sources: Mechanics and Architectural Transfer
During workbook development, teams often build visualizations against local extracts, CSVs, or development databases before transitioning to production cloud warehouses or governed Published Data Sources. Tableau provides a dedicated command to swap connections without rebuilding sheets: Data > Replace Data Source... .
The Replacement Procedure Step 1: Connect to the New Data Source in the existing workbook.
Step 2: Ensure both Old Data Source and New Data Source appear in the Data pane.
Step 3: Navigate to the top menu: Data > Replace Data Source...
Step 4: In the dialog, select:
- Current: [Old_Development_Extract]
- Replacement: [New_Production_Snowflake]
Step 5: Click OK.
Step 6: Inspect workbook for broken fields.
Step 7: Close the Old Data Source (Right-click Old Source > Close).
How References Transfer
Tableau remaps all workbook objects—including worksheet shelves, marks cards, calculated fields, parameters, groups, sets, and dashboard actions—from the old source to the new source based strictly on field names (case-sensitive) and data type compatibility .
If a field is named [Customer ID] in both sources with the same data type, all worksheet pills and calculations transition seamlessly.
If the new source renames the column to [Cust_ID] or changes its data type, the connection breaks.
Fixing Broken Calculated Fields via Replace References
When field names do not match between data sources, pills on shelves turn red, and calculated fields display a red exclamation point ( ! ) indicating invalid references.
The Anti-Pattern: Manual Sheet-by-Sheet Rebuilding
Manually dragging new fields onto every broken shelf across 30 worksheets is labor-intensive, error-prone, and leaves broken calculated fields intact.
The Best Practice: "Replace References..."
Tableau provides an automated, global remediation tool called Replace References :
In the Data pane, locate the broken field marked with a red exclamation point ( ! ) or a field containing an obsolete name.
Right-click the field and select Replace References... .
In the dialog, select the replacement field from the new data source.
Click OK .
Impact : Tableau instantly updates every calculation, worksheet shelf, filter, set, and dashboard action throughout the entire workbook that relied on the old field, transferring the reference to the new field simultaneously.
- Practical Scenarios & Migration Walkthrough
- Scenario: Migrating from Legacy Excel to Snowflake Published Source
A reporting team migrates a 25-sheet financial dashboard from an ad-hoc Excel sheet to a certified Snowflake Published Data Source. In the Excel file, the customer key was named Client Num , while in Snowflake it is named Customer ID .
The author opens the workbook and connects to the certified Snowflake Published Data Source.
The author executes Data > Replace Data Source... , setting Current to Excel_Sales and Replacement to Snowflake_Sales .
Tableau switches the active connection. Five calculated fields referencing [Client Num] immediately display red exclamation marks ( ! ).
Instead of editing each calculation individually, the author right-clicks Client Num in the Data pane, clicks Replace References... , and selects Customer ID .
All red exclamation marks disappear, all 25 worksheets render cleanly, and the author safely right-clicks Excel_Sales and selects Close .
Exam Traps & Critical Distinctions
Data Interpreter Modifies Schema Interpretation, Not Files on Disk : Data Interpreter never alters or rewrites the underlying Excel or CSV file. It merely modifies Tableau's reading query and metadata mapping.
Quick Split Delimiter Inflexibility : Quick Split automatically determines the delimiter. If an address contains both commas and spaces and you only want to split at the comma, Quick Split will fail; you must use Custom Split.
Closing Old Source Too Early : If an author closes the old data source before replacing references or verifying calculations, any calculated fields dependent exclusively on old fields may be permanently deleted or corrupted.
Aliases on Published Data Sources : The exam frequently tests whether an author can edit aliases on a published data source in Desktop. The answer is No —aliases must be created upstream before publishing or replaced with CASE calculations/groups.