Tableau Prep – Up & Running

II: Data Types
7. Dealing with Numbers
What Do We Mean by Numbers?
Types of Numbers
Category or Measure?
Aggregation
Formatting Numbers
Functions for Mastering Numerical Data
Summary
8. Dealing with Dates
Why Are Dates Important?
Parts of a Date
Date Lookup Tables
Epoch Dates
Excel Serial Number
Entering Dates
The makedate() Function
The dateparse() Function
Summary
9. Dealing with String Data
What Do We Mean by Strings?
How String Data Is Different
Character Order
Formatting Considerations
Common Functions for Preparing String Data
Grouping and Replace Options for Working with String Data
Summary
10. Dealing with Boolean Data
What Is Boolean Data?
Why Is It So Useful in Data Analysis?
Functions Featuring Boolean Logic
Summary

III: The Shape of Data
11. Profiling Data
What Is a Profile?
Why Visualizing the Data Set Is Important
Anscombe’s Quartet
Visualizations Versus Data Tables
How Prep Builder Profiles Data
Generating Histograms and Mini-Histograms
Selecting Summary Versus Detail Views
Highlighting Values
Viewing Dimension Counts
Sorting
Summary
12. Sampling Data Sets
One Simple Rule: Use It All If Possible
Sampling to Work Around Technical Limitations
Volume of Data
Velocity of Data
Other Reasons for Sampling
Reduce Build Times
Determine What You Need
Sampling Techniques
Fixed Number of Rows
Random Sample
When Not to Sample
Summary
13. Pivoting Columns to Rows
When to Pivot in Tableau Prep Builder
How to Pivot Columns to Rows
Summary
14. Pivoting Rows to Columns
When to Use a Rows-to-Columns Pivot
How to Pivot Rows to Columns
Summary
15. Aggregating in Prep Builder
Comparing Calculations in Prep Builder and Desktop
Which Calculations in Prep Builder Differ?
Adding the Aggregate Step
Where’s the Rest of My Data?
Level of Detail Calculation Option
Summary
16. Joining Data Sets Together
How to Join Data Sets in Prep Builder
Join Logic and Terminology
Types of Join in Prep Builder
When to Use Each Join Type
Summary
17. Unioning
What Is a Union?
What If the Data Structure Isn’t Identical?
When to Union Data
Monthly Data Sets
Data Sets from Web Sources
Company Mergers
Multiple Tables and Wildcard Unions
Summary
18. Calculations
What Do Calculations Do in Data Preparation?
Creating a Calculated Field
Fundamentals of Calculations
The Reference List
Syntax
Description
Example
Building the Calculation
When Calculations Go Well
When Calculations Go Poorly
Editing Calculated Fields
Recommendations
Types of Calculations
Numerical Calculations
String Calculations
Date Calculations
Conditional Calculations with a Boolean Output
Logical Calculations
Type Conversions
Level of Detail and Ranking Calculations
Summary

IV: Output
19. Choosing an Output
Types of Output
Publish to Files
Publish to Tableau Server
When to Output Data in Prep Builder
Outputting Data in the Output Step
Previewing Output Data in Desktop
Other Considerations for Output Data
Summary
20. Outputting to a Database
When to Write to a Database
Clean Data
Simplified Joins
Staging and Reference Tables
Setup for Writing to a Database
What to Watch Out For
Summary
21. Getting Started with Tableau Prep Conductor
When to Use Prep Conductor
How to Get Prep Conductor
Loading a Flow to Prep Conductor
Other Benefits of Using Prep Conductor
Summary

V: Cleaning Data
22. Creating Additional Data
When Not to Create Data
Dynamic Calculations in Desktop
Duplicate Records from Joins
Creating Additional Columns
Using Calculations
Pivoting Rows to Columns
Joining Data Sets
Creating Additional Rows
Pivoting Columns to Rows
Unioning Data Sets
Scaffolding Data Sets
Joining Data Sets
Summary
23. Filtering
What Is a Filter?
Different Types of Filters
Selection
Calculation
Wildcard
Null Values
When to Filter Out Columns
When to Filter Out Rows
Summary
24. Removing Data During Input
Changing Your Data Set Before Loading It
Slow Performance, Slow Build, Slow Output
Removing Columns
Removing Records
Summary
25. Splitting Data Fields
Basic Splits
Advanced Splits: When Automatic Splits Don’t Work as Intended
When Not to Split Data
Address Data
No Clear Delimiter
Summary
26. Cleaning by Grouping Data
What Does Grouping Mean?
Why Use Grouping
Improving Accuracy
Navigating the Data Hierarchy
Smoothing Reorganizations
Grouping Techniques
Manual
Calculations
Built-in Functionality
Summary
27. Dealing with Nulls
What Is a Null?
When Is a Null OK?
How to Remove or Replace a Null
ISNULL()
ZN()
Merge
Summary
28. Using Data Roles
How to Use Data Roles
Custom Data Roles
Summary
29. Dealing with Unwanted Characters
What Is an Unwanted Character?
Issues Caused by Unwanted Characters
Removing Unwanted Characters
Strings with Mistyped Characters
Numbers with Unwanted Characters
Dates with Mistyped Characters
Summary
30. Deduplicating
How to Identify Duplicates
Causes of Duplicates
System Loads
Row per Measure
Joins
How to Handle Duplicates
Aggregating: Technique 1
Aggregating: Technique 2
Pivoting Rows to Columns
Summary
31. Using Regular Expressions
What Are Regular Expressions?
How to Use Regexes in Prep
REGEXP_EXTRACT() and REGEXP_EXTRACT_NTH()
REGEXP_MATCH()
REGEXP_REPLACE()
Regex Use Cases
Replacing Common Mistakes
Anonymizing Comments or Feedback
Common Regex Commands
Summary
32. Completing Advanced Joins
Multiple Join Conditions
Join Conditions Other Than Equals
Filtering with a Join
Joining by a Range
OR Statements
Summary
33. Creating Level of Detail Calculations
What Is Appending?
Exploring Appending Through LOD Calculations
When to Use an LOD Calculation
How to Write an LOD Calculation in Prep Builder
What a Level of Detail Calculation Is Doing
Summary
34. Doing Analytical Calculations
What Is a Table Calculation?
Applying Table Calculation Logic in Prep Builder
Keywords
Analytical Calculations
Use Cases
Filtering for the Top N
Filtering Out a Percentage of Data
Summary

VI: Beyond the Basics
35. Breaking Down Complex Data Preparation Challenges
The Challenge
Where to Begin
Logical Steps
Making Changes
Be Ready to Iterate
Summary
36. Handling Free Text
What Is Free Text?
Why Is Free Text Useful?
How to Analyze Free Text in Tableau
Split the Strings
Pivot Columns to Rows
Clean Cases and Punctuation
Use a Join to Remove Common Words
Group the Remaining Values
Summary
37. Using Smarter Filtering
Calculations
Boolean Calculations
Logical Calculations
Regex Calculations
Join Ranges
Percentage Variance
Manual Entry: Level of Detail Calculations
Reloaded Data: Join to Previous Output
Aggregating the Average Production Cost per Type
Joining the Data Sets Together
Combining Techniques
Summary
38. Managing Conversion Rates
Challenges of Conversion Rates
Applying Conversion Rates in Prep
Step 1: Create a Consistent Granularity of Data for the Conversion
Step 2: Join the Data Sets Together
Step 3: Apply the Conversion Rate
Long-Term Strategies for Conversion Rates
Managing Frequency
Maintaining History Tables
Summary
39. Scaffolding Your Data
What Is Scaffolding?
Challenges Addressed by Scaffolding
Challenges Created by Scaffolding
The Traditional Scaffolding Technique
Step 1: Input the Data Sets
Step 2: Build the Join Calculations
Step 3: Join the Two Data Sets Together
Step 4: Filter Out Unnecessary Rows
The Newer Scaffolding Technique
Step 1: Input the Data Sets
Step 2: Join the Data Sets
Step 3: Add the Reporting Date
Step 4: Remove the Scaffold Value
The Result
Summary
40. Connecting to Programming Scripts
When to Use the Script Step in Prep
Setting Up Your Computer to Use Scripts in Prep
Using a Script Step
Summary
41. Handling Prep Builder Errors
Parameter Errors
Blank Profile Panes or Data Panes
Changing a Calculation or Removing a Data Field Downstream
The Data Source Has Changed
Errors Within a Calculated Field
Incomplete Calculations
Unsupported Functions
Summary

VII: Managing Your Data
42. Documenting Your Data Preparation
Basic Documentation
Folder Structure
Filenames
Data Sources
Output
Step Names
Clean Step
Step Descriptions
Color
Joins
Unions
Summary
43. Deciding Where to Prepare Your Data
Processes to Consider
Data Preparation Versus Visual Analytics
Data Literacy
Organization Size
Quality of Technological Hardware
History of Data Investment
Software Performance
Sampling
Functionality
Documentation
Summary
44. Managing Data
What Is Sensitive Data?
Public
Confidential
Strictly Confidential
Restricted
Managing Data Based on Sensitivity
Production Versus Development Environments
Deleting Data
When Data Becomes Outdated or Irrelevant
When a Customer or Client Leaves
Summary
45. Storing Your Data
Inaccessibility
Don’t Break the Law
Don’t Delete Operational Data
Do Grant Access to Data for the Experts
Do Document Your Sources
Slow/Unresponsive Performance
Overwriting Risks
Grant Read-Only Access
Train Before Publishing
So, Where Do You Write That Output?
Summary
46. Using Identifiers and Keys in Data
What Is an Identifier?
What Is a Key in a Database?
Using Keys and Identifiers in Prep
Creating Identifier Data Fields in Prep Builder
Summary
47. Keeping Your Data Up-to-Date
Refreshing Data
Full Versus Incremental Refreshes
Setting Up Different Types of Refresh
Full Refresh
Incremental Refresh
What to Watch Out for When Refreshing Data Sources
Changing Data Values
Altering the Structure of Sources
New Data, New Input
Summary
48. Using History Tables
Why Are History Tables Required?
What to Consider When Creating History Tables
Ability to Join to Live Data
Relevance of Information
Frequency of Updates
Level of Granularity
Performance
Data Regulations
An Example History Table
Summary
49. Evaluating Whether You Need Prep Builder at All
A History of Data Preparation in Tableau
Where to Try Desktop First
Simple Joins
Unions
Single Pivots
Where to Start with Prep Builder
Summary
50. Final Thoughts

Index