Master Tableau From Scratch

Learn Tableau fundamentals, data connections, data preparation, filtering, data modeling, calculations, LOD expressions, visualizations, dashboards, and storytelling through structured practical lessons.

COURSE SYLLABUS

Data Splitting and Pivoting in Tableau

Learn how to split combined fields into separate columns and transform data using Pivot in Tableau. Understand standard Split, Custom Split, Columns to Rows, Rows to Columns, and practical data preparation techniques.

1. Introduction to Data Splitting and Pivoting

Data preparation is an important step before creating visualizations in Tableau. Real-world datasets are not always structured in the format required for analysis. Sometimes multiple values are stored in a single field, or related values are spread across multiple columns.

Tableau provides data preparation capabilities that help reorganize these structures. Two useful concepts are Splitting and Pivoting.

What Is Data Splitting?

Data splitting separates a single field containing multiple values into multiple fields using a delimiter or a specified pattern.

What Is Pivoting?

Pivoting changes the orientation of data by converting columns into rows or rows into columns. This helps reorganize datasets into a structure that is easier to analyze.

2. Understanding Data Splitting

Data splitting is useful when a single column contains multiple pieces of information. For example, a field named Full Name may contain both first and last names separated by a space.

By splitting the field, you can create separate columns that can be used independently in filters, calculations, and visualizations.

Example: Full Name

Full Name
Rahul Sharma
Priya Patil
Amit Deshmukh

After splitting the Full Name field, Tableau can create separate fields for First Name and Last Name.

Data Splitting Full Name Rahul Sharma Priya Patil First Name Last Name Rahul Sharma

A combined text field can be separated into multiple fields.

3. Standard Split in Tableau

Standard Split automatically separates a field using a delimiter that Tableau identifies in the data. Common delimiters include spaces, commas, hyphens, and other characters.

Example Dataset

Employee Details
101-Rahul-Pune
102-Priya-Mumbai
103-Amit-Nashik

How to Perform Standard Split

  1. Connect Tableau to your dataset.
  2. Open a worksheet and locate the field in the Data Pane.
  3. Right-click the field you want to split.
  4. Open the Transform option if available.
  5. Select Split.
  6. Tableau creates additional split fields based on the detected delimiter.
  7. Review the generated fields and their values.

Expected Result

Employee ID Name City
101RahulPune
102PriyaMumbai
103AmitNashik
Tip: Standard Split is useful when the delimiter pattern is consistent across the records.

4. Custom Split in Tableau

Custom Split allows users to specify the delimiter and the number of split fields required. It is useful when the default split behavior does not produce the desired result.

Example: Email Address

Email Address
rahul@gmail.com
priya@yahoo.com
amit@outlook.com

The @ character can be used as a delimiter to separate the username from the domain.

How to Perform Custom Split

  1. Locate the required field in the Data Pane.
  2. Right-click the field.
  3. Open the Transform option.
  4. Select Custom Split.
  5. Enter the delimiter, such as @.
  6. Specify the required split position or number of fields when prompted.
  7. Apply the split and inspect the resulting fields.
Custom Split Using @ Email Address rahul@gmail.com Username Domain rahul gmail.com Delimiter: @

Custom Split separates an email address using the @ delimiter.

Best Practice: Use a delimiter that consistently separates the required values. Review records containing extra delimiters or missing parts.

5. Difference Between Standard Split and Custom Split

Feature Standard Split Custom Split
Delimiter Tableau identifies the delimiter automatically. User specifies the delimiter.
Configuration Requires fewer settings. Allows more control over the split.
Usage Useful for consistent, common patterns. Useful for specific or customized patterns.
Example Splitting a name using spaces. Splitting an email using @.

6. Understanding Pivoting in Tableau

Pivoting reorganizes data by changing its orientation. It is commonly used when related values are stored in separate columns but need to be represented as rows.

For example, a sales dataset may contain separate columns for January, February, and March. Pivoting these columns creates a more analysis-friendly structure with one Month field and one Sales field.

Before Pivot After Pivot Region Jan Feb Mar East 100 150 200 West 120 170 220 Region Month Sales East Jan 100 East Feb 150 East Mar 200 West Jan 120 West Feb 170

Pivot converts multiple month columns into Month and Sales rows.

7. Pivot Columns to Rows

Columns to Rows is one of the most common pivot operations. It converts selected columns into row values and creates two resulting fields: Pivot Field Names and Pivot Field Values.

Example: Monthly Sales

Suppose a dataset contains Region, January Sales, February Sales, and March Sales. These monthly fields can be converted into a single Month field and a corresponding Sales field.

Region January February March
East100015002000
West120017002200

How to Pivot Columns to Rows

  1. Open the Data Source page.
  2. Select the table containing the monthly columns.
  3. Select the columns you want to pivot.
  4. Use Ctrl or the appropriate selection method to select multiple columns.
  5. Right-click the selected columns.
  6. Select Pivot from the available menu.
  7. Tableau creates Pivot Field Names and Pivot Field Values.
  8. Rename the generated fields to meaningful names.

After Pivoting

Region Month Sales
EastJanuary1000
EastFebruary1500
EastMarch2000
WestJanuary1200
WestFebruary1700
WestMarch2200
Tip: Rename Pivot Field Names to Month and Pivot Field Values to Sales when those names accurately describe the resulting data.

8. Understanding Rows to Columns

Rows to Columns is the reverse transformation of Columns to Rows. It reorganizes row-based values into separate columns based on a selected field.

This operation is useful when data is stored in a normalized row-based format but a wider presentation is required.

Example: Before Transformation

Region Month Sales
EastJanuary1000
EastFebruary1500
WestJanuary1200
WestFebruary1700

After Transformation

Region January February
East10001500
West12001700

In Tableau, the exact available pivot operations depend on the connector and the data source. Tableau's native pivot functionality in the Data Source page is commonly used to convert columns into rows. If the reverse transformation is required, use an appropriate reshaping method in the source system or a supported preparation tool.

Important: Do not assume that every Tableau connector provides a direct Rows to Columns pivot command. Check the available data preparation options for your connection.

9. Understanding Pivot Field Names and Pivot Field Values

When you pivot columns to rows in Tableau, two generated fields are commonly created.

Pivot Field Names

This field contains the original column names that were converted into row values. For example, it may contain January, February, and March.

Pivot Field Values

This field contains the corresponding values from the original columns. For example, it may contain sales amounts such as 1000, 1500, and 2000.

Generated Field Example Values Meaning
Pivot Field Names January, February, March Identifies the original column.
Pivot Field Values 1000, 1500, 2000 Contains the corresponding data values.

10. Difference Between Splitting and Pivoting

Basis Splitting Pivoting
Purpose Separates combined field values. Changes the orientation of data.
Input Usually one field containing multiple values. Usually multiple related columns or row values.
Output Multiple separate fields. A reorganized table structure.
Example Full Name into First Name and Last Name. January, February, March columns into Month and Sales rows.
Use Case Cleaning combined text fields. Preparing data for time-based analysis.

11. Practical Workflow in Tableau

1. Connect to the Dataset 2. Inspect the Data Structure 3. Split Combined Fields 4. Pivot Required Columns 5. Validate the Resulting Data

A practical workflow for splitting and pivoting data.

Step-by-Step Process

  1. Open Tableau and connect to an Excel or CSV dataset.
  2. Open the Data Source page.
  3. Inspect the columns and identify combined fields.
  4. Apply Split or Custom Split where appropriate.
  5. Review the generated fields.
  6. Identify columns that should be converted into rows.
  7. Select the required columns and apply Pivot.
  8. Rename the generated pivot fields.
  9. Validate the resulting records and data types.
  10. Open a worksheet and use the prepared fields in a visualization.

12. Common Mistakes and Troubleshooting

1. Incorrect Delimiter

If the wrong delimiter is selected, the split may produce incorrect values. Inspect the source field and choose the correct delimiter.

2. Inconsistent Data Patterns

Records with different formats may not split into the expected fields. Review inconsistent records and handle exceptions separately.

3. Unexpected Split Columns

Standard Split may create more fields than required when records contain multiple delimiters. Review the generated fields and consider Custom Split.

4. Incorrect Pivot Selection

Selecting unrelated columns can produce an incorrect data structure. Pivot only fields that represent the same type of information.

5. Incorrect Field Names After Pivot

Rename Pivot Field Names and Pivot Field Values according to the meaning of the resulting data.

6. Missing or Unexpected Values

Check whether the original columns contain NULL values or inconsistent data before performing the transformation.

7. Data Type Issues

Verify the data types of the generated fields. Pivoted values may require additional type validation before analysis.

13. Best Practices

  • Inspect source values before applying a split.
  • Choose delimiters that consistently separate the required information.
  • Use Custom Split when automatic splitting is not sufficient.
  • Pivot only related columns that represent the same business measure.
  • Rename generated fields using meaningful names.
  • Check for NULL values and inconsistent records after transformations.
  • Validate record counts and important totals after pivoting.
  • Keep the original source data available for comparison.
Industry Tip: Splitting and pivoting should make data easier to analyze without changing its business meaning. Always validate the transformed data before publishing reports.

14. Practical Exercise

Exercise A: Split Employee Details

Create or use a dataset containing an Employee Details field with values such as:

101-Rahul-Pune
102-Priya-Mumbai
103-Amit-Nashik
  1. Connect Tableau to the dataset.
  2. Locate the Employee Details field.
  3. Apply a split using the hyphen delimiter.
  4. Review the generated fields.
  5. Rename them to Employee ID, Employee Name, and City.
  6. Validate the resulting values.

Exercise B: Pivot Monthly Sales

Use a dataset containing Region, January, February, and March sales columns.

  1. Connect Tableau to the monthly sales dataset.
  2. Open the Data Source page.
  3. Select the January, February, and March columns.
  4. Apply Pivot.
  5. Rename Pivot Field Names to Month.
  6. Rename Pivot Field Values to Sales.
  7. Open a worksheet and create a monthly sales chart.
  8. Compare the totals with the original dataset.

15. Practice and Interview Questions

Q1. What is data splitting in Tableau?

Data splitting separates a field containing multiple values into multiple fields using a delimiter or pattern.

Q2. What is the difference between Standard Split and Custom Split?

Standard Split identifies a delimiter automatically, while Custom Split allows users to specify the delimiter and split configuration.

Q3. What is pivoting in Tableau?

Pivoting reorganizes data by changing its orientation. A common operation converts multiple columns into rows.

Q4. What are Pivot Field Names and Pivot Field Values?

Pivot Field Names stores the original column names, while Pivot Field Values stores their corresponding data values.

Q5. Why do we use pivoting?

Pivoting helps reorganize wide datasets into a row-based structure that is easier to filter, aggregate, and analyze.

Q6. Can we split an email address in Tableau?

Yes. A Custom Split using the @ delimiter can separate the username and domain.

Q7. What is the difference between splitting and pivoting?

Splitting separates combined values within a field. Pivoting changes the arrangement of data across columns and rows.

Q8. What should you verify after pivoting?

Verify field names, data types, record counts, missing values, and important totals.

16. Summary

Data Splitting and Pivoting are useful data preparation techniques in Tableau. Splitting separates combined information into individual fields, making it easier to filter, group, and analyze specific values.

Standard Split and Custom Split provide different ways to separate data using delimiters. Pivoting reorganizes the structure of a dataset, commonly converting multiple columns into rows with Pivot Field Names and Pivot Field Values.

By applying these techniques carefully, validating the transformed records, and using meaningful field names, analysts can prepare structured datasets for reliable Tableau worksheets, reports, and dashboards.