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.
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
- Connect Tableau to your dataset.
- Open a worksheet and locate the field in the Data Pane.
- Right-click the field you want to split.
- Open the Transform option if available.
- Select Split.
- Tableau creates additional split fields based on the detected delimiter.
- Review the generated fields and their values.
Expected Result
| Employee ID | Name | City |
|---|---|---|
| 101 | Rahul | Pune |
| 102 | Priya | Mumbai |
| 103 | Amit | Nashik |
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
- Locate the required field in the Data Pane.
- Right-click the field.
- Open the Transform option.
- Select Custom Split.
- Enter the delimiter, such as @.
- Specify the required split position or number of fields when prompted.
- Apply the split and inspect the resulting fields.
Custom Split separates an email address using the @ delimiter.
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.
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 |
|---|---|---|---|
| East | 1000 | 1500 | 2000 |
| West | 1200 | 1700 | 2200 |
How to Pivot Columns to Rows
- Open the Data Source page.
- Select the table containing the monthly columns.
- Select the columns you want to pivot.
- Use Ctrl or the appropriate selection method to select multiple columns.
- Right-click the selected columns.
- Select Pivot from the available menu.
- Tableau creates Pivot Field Names and Pivot Field Values.
- Rename the generated fields to meaningful names.
After Pivoting
| Region | Month | Sales |
|---|---|---|
| East | January | 1000 |
| East | February | 1500 |
| East | March | 2000 |
| West | January | 1200 |
| West | February | 1700 |
| West | March | 2200 |
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 |
|---|---|---|
| East | January | 1000 |
| East | February | 1500 |
| West | January | 1200 |
| West | February | 1700 |
After Transformation
| Region | January | February |
|---|---|---|
| East | 1000 | 1500 |
| West | 1200 | 1700 |
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.
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
A practical workflow for splitting and pivoting data.
Step-by-Step Process
- Open Tableau and connect to an Excel or CSV dataset.
- Open the Data Source page.
- Inspect the columns and identify combined fields.
- Apply Split or Custom Split where appropriate.
- Review the generated fields.
- Identify columns that should be converted into rows.
- Select the required columns and apply Pivot.
- Rename the generated pivot fields.
- Validate the resulting records and data types.
- 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.
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
- Connect Tableau to the dataset.
- Locate the Employee Details field.
- Apply a split using the hyphen delimiter.
- Review the generated fields.
- Rename them to Employee ID, Employee Name, and City.
- Validate the resulting values.
Exercise B: Pivot Monthly Sales
Use a dataset containing Region, January, February, and March sales columns.
- Connect Tableau to the monthly sales dataset.
- Open the Data Source page.
- Select the January, February, and March columns.
- Apply Pivot.
- Rename Pivot Field Names to Month.
- Rename Pivot Field Values to Sales.
- Open a worksheet and create a monthly sales chart.
- 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.