Pivoting Data in Tableau

Definition: Pivoting data in Tableau is a data preparation technique used to transform data from a wide format into a tall format by converting multiple columns into rows. It helps organize data so that similar types of information are stored in common fields, making analysis, filtering, aggregation, and visualization easier.

Example: If a sales dataset contains separate columns for January, February, and March sales, pivoting can combine these columns into a single field called Month and place the corresponding amounts in another field called Sales.

Course: JustAcademy Tableau Training

Course Demo: Register for a Tableau Course Demo

1. What Is Pivoting Data?

Pivoting is used when a dataset contains the same type of information in multiple columns. Instead of keeping each category or period in a separate column, Tableau can convert those columns into rows and create two new fields: one for the original column names and another for their values.

In Tableau Desktop, the term pivot commonly refers to converting columns into rows. This helps transform wide, cross-tabulated data into a structure that is easier to analyze.

Example of Data Before Pivoting

In this structure, each month has a separate column. Analyzing monthly performance across a longer period may require working with many individual fields.

Example of Data After Pivoting

After pivoting, Tableau creates a row for each employee and month combination. The default field names are Pivot Field Names and Pivot Field Values. You can rename them to Month and Sales, respectively, when appropriate.

2. Why Is Pivoting Important in Tableau?

• Improves data structure: Converts repeated column patterns into a consistent row-based structure.
• Simplifies analysis: Makes it easier to analyze a common measure across months, years, subjects, or categories.
• Supports visualizations: Allows fields such as Month and Sales to be used together in charts.
• Reduces repetitive fields: Avoids having to work separately with many similar columns.
• Supports filtering: Makes it easier to filter a common field such as Month or Type.
• Helps compare categories: Makes it easier to compare different periods or groups using the same measure.
• Prepares data for analysis: Transforms cross-tabulated spreadsheets into a more analysis-friendly structure.

3. Wide Data vs. Tall Data

Wide Data

Wide data stores different categories, periods, or measurements in separate columns.

Tall Data

Tall data stores the category in one field and the associated value in another field.

Pivoting the subject columns into rows makes it easier to create a chart comparing scores by subject. Tableau uses the term pivot for this columns-to-rows transformation; terminology can differ in other data tools.

4. Prerequisites for Pivoting Data

• Tableau Desktop or another supported Tableau authoring environment.
• A connected data source, such as an Excel file or text file.
• At least two columns that contain the same general type of information to pivot.
• Clear column headers and a consistent data structure.
• An understanding of which columns should remain unchanged and which columns should be converted into rows.

Important: Tableau Desktop's standard Pivot option is available for supported sources such as Microsoft Excel, text files, Google Sheets, and PDF data sources. For other source types, options such as custom SQL may be required.

5. How to Pivot Data in Tableau Desktop

Step 1: Connect to Your Data

1. Open Tableau Desktop.
2. Under Connect, select the appropriate data source, such as Microsoft Excel or a text file.
3. Choose your file and open it.
4. Review the tables and columns displayed on the Data Source page.

Step 2: Identify the Columns to Pivot

Identify the columns that contain similar types of information. For example, January, February, and March might contain monthly sales amounts.

Do not select columns that identify each record, such as Employee ID or Employee Name, unless they are specifically intended to be transformed.

Step 3: Select Two or More Columns

1. On the Data Source page, locate the data preview grid.
2. Select the column headers that you want to pivot.
3. Use Ctrl-click on Windows or the equivalent selection method for your system to select multiple non-adjacent columns, if supported by the interface.
4. Make sure that all selected columns belong to the same intended group of measurements.

Step 4: Apply the Pivot Option

1. Click the drop-down arrow beside one of the selected column headers.
2. Choose Pivot.
3. Tableau converts the selected columns into rows.
4. Two new fields appear: Pivot Field Names and Pivot Field Values.

Step 5: Rename the Pivot Fields

Rename the new fields to meaningful names based on their contents. For example:

• Pivot Field Names → Month
• Pivot Field Values → Sales

Use names that accurately describe the data. If the original column names represent subjects, years, or product types rather than months, choose appropriate field names.

Step 6: Verify the Results

1. Review the data preview grid.
2. Check that the original identifying columns remain intact.
3. Confirm that the pivoted field contains the expected column names.
4. Check that the values are associated with the correct records.
5. Review data types and any null values before beginning analysis.

Step 7: Create a Visualization

1. Open a worksheet.
2. Drag Month to Columns.
3. Drag Sales to Rows.
4. Use Employee Name, Category, or another relevant dimension to compare groups.
5. Choose an appropriate chart type, such as a line chart for monthly trends.

Official guide: Tableau Help: Pivot Data from Columns to Rows.

6. Understanding Pivot Field Names and Pivot Field Values

Pivot Field Names

This field stores the original column headers that were converted into row values.

Example: January, February, and March become values in the Month field.

Pivot Field Values

This field stores the corresponding values from the original selected columns.

Example: Sales amounts such as 12000, 15000, and 18000 become values in the Sales field.

These two fields allow Tableau to treat the original column headers as data values and the original cell contents as a single measure field.

7. Adding More Columns to an Existing Pivot

If you need to include additional columns after creating a pivot, you may be able to extend the pivot rather than starting again.

1. Open the Data Source page.
2. Locate the columns that should be added to the existing pivot.
3. Select the relevant additional column.
4. Click the drop-down arrow beside its header.
5. Choose Add Data to Pivot, when available.
6. Review the pivoted fields and confirm that the added values are correct.

The available options depend on the data source and the current pivot configuration.

8. Removing a Pivot

If the pivot is no longer needed, you can remove it from the data source configuration.

1. Open the Data Source page.
2. Locate a pivot field or column header associated with the pivot.
3. Click the drop-down arrow.
4. Select Remove Pivot, if available.
5. Review the resulting data structure before continuing your analysis.

Depending on the changes you need to make, you may also be able to undo the operation or modify the pivot configuration.

9. Pivoting Data Using Custom SQL

For certain database sources where the standard Pivot option is not available, Tableau Desktop can use custom SQL to reshape columns into rows. A common approach uses UNION ALL to combine values from different columns into a single field.

Example: Start Time and End Time

Suppose a table called Contest contains Runner, Start Time, and End Time. The goal is to create one Action field and one Time field.

SELECT
    Runner,
    'Start' AS Action,
    [Start Time] AS Time
FROM Contest
UNION ALL
SELECT
    Runner,
    'End' AS Action,
    [End Time] AS Time
FROM Contest;

Explanation:

• The first query places the Start Time values in the Time field and labels the corresponding rows Start.
• The second query places the End Time values in the same Time field and labels the corresponding rows End.
• UNION ALL combines the rows returned by both queries.

The exact syntax for quoting identifiers varies by database. For example, square brackets are supported by SQL Server but not by every database engine.

Expected structure:

This approach reshapes the values into a row-based structure suitable for analysis. Confirm that the selected fields have compatible data types before combining them.

10. Pivoting Data in Tableau Desktop vs. Tableau Prep

Official Tableau Prep guide: Tableau Prep Help: Pivot Your Data.

11. Common Problems and Troubleshooting

12. Best Practices for Pivoting Data

• Understand the meaning of each column before pivoting.
• Keep identifier fields such as Employee ID, Order ID, and Customer ID unchanged when they define individual records.
• Pivot columns that represent the same kind of information, such as monthly sales or subject scores.
• Rename Pivot Field Names and Pivot Field Values to meaningful names.
• Check data types after pivoting, especially dates and numeric values.
• Inspect null values, blank rows, missing headers, and inconsistent source columns.
• Validate totals before and after pivoting to confirm that the transformation preserves the intended data.
• Update calculations and worksheets that depend on the original columns.
• Document the reason for the pivot so that other workbook users can understand the data preparation process.
• If new columns may be added regularly, consider whether Tableau Prep's wildcard pivot is more appropriate for the workflow.

13. Practical Exercise

Task: Pivot monthly sales data and create a visualization that compares sales across months.

Sample Input Data

Instructions

1. Connect Tableau to the sample Excel file.
2. Open the Data Source page.
3. Select January, February, March, and April.
4. Choose Pivot.
5. Rename Pivot Field Names to Month and Pivot Field Values to Sales.
6. Open a worksheet.
7. Drag Month to Columns and Sales to Rows.
8. Drag Product to Color to compare products.
9. Choose a line chart or bar chart.
10. Verify the totals and ensure that the months are displayed in the correct order.

Expected Learning Outcome

You should be able to transform wide sales data into a tall format and create a chart using the common Month and Sales fields.

14. Interview Questions and Answers

Q1. What is pivoting in Tableau?

Pivoting in Tableau Desktop commonly means converting selected columns into rows to create a more analysis-friendly data structure.

Q2. Why is pivoting useful?

It consolidates repeated information from multiple columns into common fields, making analysis and visualization easier.

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

Pivot Field Names stores the original column headers as values, while Pivot Field Values stores the corresponding values from those columns.

Q4. What is the difference between wide and tall data?

Wide data stores categories or periods in separate columns. Tall data stores those categories in rows and their associated values in a common field.

Q5. How do you pivot data in Tableau Desktop?

Connect to a supported source, open the Data Source page, select two or more columns, open the column menu, and choose Pivot.

Q6. Why might the Pivot option be missing?

The source may not support the standard Desktop pivot option, or fewer than two columns may be selected. For other database sources, custom SQL may be an alternative.

Q7. Can you add more columns to an existing pivot?

Yes, when supported, use Add Data to Pivot from the relevant column menu.

Q8. What happens to calculations that use the original columns?

They may become invalid because the pivot replaces the original selected fields. Update calculations and worksheet references to use the new pivot fields.

Q9. Is Tableau Desktop pivoting the same as Tableau Prep pivoting?

No. Tableau Desktop's standard pivot operation focuses on converting columns to rows for analysis. Tableau Prep supports additional pivot workflows, including rows to columns and wildcard-based pivots in supported versions.

Q10. How can pivoted data be used in a chart?

Place the pivoted category field, such as Month, on Columns and the values field, such as Sales, on Rows. Add another dimension to compare groups.

15. Quick Revision Checklist

• Understand the meaning of pivoting data in Tableau.
• Identify when data is stored in a wide format.
• Know the difference between wide data and tall data.
• Practice selecting columns and using the Pivot option.
• Understand Pivot Field Names and Pivot Field Values.
• Rename the new fields clearly.
• Know how to add more columns to a pivot or remove a pivot.
• Understand when custom SQL may be required.
• Recognize common pivot-related errors and update broken references.
• Validate the transformed data before building visualizations.

16. Learning Resources

• JustAcademy Tableau Training — Explore Tableau training.
• Register for a Tableau Course Demo — Register for a course demonstration.
• Tableau Desktop: Pivot Data from Columns to Rows — Learn the standard Desktop pivot process and troubleshoot common issues.
• Tableau Prep: Pivot Your Data — Learn about columns-to-rows, rows-to-columns, and wildcard pivot options in Tableau Prep.

Conclusion

Pivoting data is an important Tableau data preparation technique that transforms multiple columns into a common set of rows and fields. It helps make wide datasets easier to analyze, filter, aggregate, and visualize. By selecting the correct columns, renaming pivot fields, checking data types, and validating results, you can prepare data for more effective Tableau analysis.