2.2 Reshaping Data in Prep: Pivots, Splits & Wildcard Unions

2.2 Reshaping Data in Prep: Pivots, Splits & Wildcard Unions

Raw enterprise data rarely arrives structured for optimal visual analysis. Financial reports, spreadsheet models, and legacy transactional extracts often store data in wide, human-readable crosstabs where columns represent dates or metrics. Conversely, other systems export data in tall Entity-Attribute-Value (EAV) key-value pairs where attributes are stacked vertically.

Tableau Prep Builder provides robust structural reshaping tools that transform uncooperative source schemas into clean, normalized models. Mastery of Pivots (Columns to Rows and Rows to Columns), String Splits, and Wildcard Unions is a foundational requirement for the Salesforce Certified Tableau Data Analyst exam.

---

Dimensional Normalization: Wide vs. Tall Data

The fundamental premise of Tableau's visual query engine (VizQL) is that dimensions slice data and measures aggregate data. To build dynamic time-series line charts, continuous date axes, and robust filters in Tableau Desktop, datasets must be formatted in a tall (normalized) structure:

• Wide (Denormalized) Data: A spreadsheet has separate columns for Jan 2026, Feb 2026, Mar 2026, through Dec 2026, with numeric revenue amounts populated in cells. In Tableau Desktop, these appear as twelve separate measure fields. Building a continuous 12-month trend line would require authoring twelve individual calculations or dragging twelve pills onto the view—a practice that breaks core visualization features.
• Tall (Normalized) Data: The twelve monthly columns are unpivoted into two standardized columns: a single dimension containing the date values (Month/Year) and a single measure containing the metric (Revenue). In Tableau Desktop, dragging Month/Year to Columns and Revenue to Rows instantly generates a continuous trend visualization.

WIDE (DENORMALIZED) FORMAT:               TALL (NORMALIZED) FORMAT:
+------------+----------+----------+      +------------+----------+---------+
| Department | Jan_2026 | Feb_2026 |      | Department | Month    | Revenue |
+------------+----------+----------+      +------------+----------+---------+
| Hardware   | $10,000  | $12,000  | ---> | Hardware   | Jan_2026 | $10,000 |
| Software   | $25,000  | $28,000  |      | Hardware   | Feb_2026 | $12,000 |
+------------+----------+----------+      | Software   | Jan_2026 | $25,000 |
                                          | Software   | Feb_2026 | $28,000 |
                                          +------------+----------+---------+

---

Pivot Types in Tableau Prep Builder

Tableau Prep provides two distinct pivot mechanisms within the Pivot step:

1. Columns to Rows Pivot (Unpivot / Wide to Tall)

This is the most frequent pivot operation in analytical workflows. It converts multiple field columns into row records.

• Configuration: Add a Pivot step to the flow. Ensure the pivot mode is set to Columns to Rows. Select the candidate columns from the Fields pane and drag them into the Pivoted Fields (Pivot Values) drop zone.
• Generated Fields: Prep automatically creates two new fields:
  • Pivot1 Names: Contains the original column header names (e.g., Jan_2026, Feb_2026).
  • Pivot1 Values: Contains the numerical or string cell values associated with those headers.
• Best Practice: Immediately rename Pivot1 Names and Pivot1 Values to intuitive business names (e.g., Order Month and Monthly Budget).

Coordinated Multi-Column Pivots

In advanced scenarios, a dataset may contain multiple metrics across identical time horizons (for example, monthly actuals alongside monthly forecasts: Actual_Jan, Forecast_Jan, Actual_Feb, Forecast_Feb). In Tableau Prep, analysts can create multiple pivot groups within a single Pivot step. By adding a second column group in the Pivoted Fields drop zone, Prep generates separate measure columns for Actuals and Forecasts while aligning them along a unified date dimension row.

2. Rows to Columns Pivot (Pivot / Tall to Wide)

The inverse transformation reshapes tall, key-value transactional tables into structured, columnar tables.

• Use Case: Commonly applied when analyzing survey data, healthcare patient observation logs, or EAV models where one column contains the question/metric name (e.g., Heart Rate, Blood Pressure, Temperature) and another contains the measurement. To analyze each metric independently in Desktop without filtering, each attribute must exist as its own distinct column.
• Configuration: In the Pivot step, toggle the mode dropdown to Rows to Columns.
• Drop Zones:
  1. Field that will pivot rows to columns: Drag the dimensional field containing the future column headers (e.g., Metric Name).
  2. Field to aggregate for new columns: Drag the field containing the cell values (e.g., Metric Value).
  3. Aggregation Function: Select the aggregation rule (e.g., SUM, AVG, MIN, MAX) applied when multiple rows match the same intersection.

Architectural Comparison: Pivot Modes

---

Splitting Complex String Fields

Data often combines multiple attributes into a single delimited text string, such as customer IDs formatted as US-2026-CA-94102 or full employee names like Smith, Dr. John A..

Tableau Prep Builder provides two splitting methods in the field context menu:

Automatic Split

Tableau Prep analyzes the underlying character distributions across the field to detect common punctuation patterns (hyphens, commas, slashes, or underscores). If a consistent pattern is identified, Prep automatically creates new split columns for all detected segments.

• Limitation: Automatic split assumes a uniform number of delimiters across all rows. If some records contain two hyphens and others contain three or four, Automatic Split may truncate or misalign downstream attributes.

Custom Split

Custom Split grants the analyst full deterministic control over text parsing:

• Separator (Delimiter): Specify the exact delimiter character (e.g., -, ,, |, or a space).
• Split off: Choose which portion of the string to extract:
  • First N: Extracts the first N elements from the beginning of the string.
  • Last N: Extracts the trailing N elements from the end of the string (ideal for extracting file extensions or zip codes regardless of leading segments).
  • All: Creates a separate column for every delimited component.
• Split count: An integer defining how many columns to generate.

---

Multi-File & Multi-Sheet Ingestion via Wildcard Union

Organizations frequently store transactional data across partitioned flat files—such as monthly revenue dumps (Sales_2026_01.csv, Sales_2026_02.csv) or regional logs stored in dedicated folders. Manually adding and unioning dozens of separate Input steps is brittle and unmaintainable.

Tableau Prep Builder automates this workflow through Wildcard Union directly within the Input step:

+-----------------------------------------------------------------------------------+
|                         WILDCARD UNION CONFIGURATION                              |
+-----------------------+-----------------------------------------------------------+
| Search Directory      | /Volumes/DataLake/Regional_Sales/                         |
+-----------------------+-----------------------------------------------------------+
| Include subfolders    | [X] Enabled (Recursively searches child directories)      |
+-----------------------+-----------------------------------------------------------+
| Matching Pattern      | Sales_2026_*.csv                                          |
+-----------------------+-----------------------------------------------------------+
| Sheet Matching (XLSX) | Q[1-4]_* (Matches quarterly tabs across workbooks)        |
+-----------------------+-----------------------------------------------------------+

Automated Lineage: File Paths & Sheet Metadata Fields

When a Wildcard Union executes, Prep automatically injects two system metadata columns into the unified dataset:

1. File Paths: Records the absolute directory path and filename of the originating file for every single row.
2. Table provenance: Multi-table or multi-sheet unions can expose a generated table-name field. Confirm its displayed name in the Input step because file and table metadata differ by connector.

Exam Best Practice: In many reporting systems, critical business attributes—such as the transaction year, region, or branch code—exist only in the file name or sheet tab and are omitted from the tabular data columns. Analysts can add a Clean step immediately following the Wildcard Union and use Custom Split on the File Paths field to extract these attributes into first-class dimensions.

---

Handling Schema Mismatches in Unions

When combining datasets using a Union step (whether via Wildcard Union or by combining separate branch nodes in the Flow pane), Tableau Prep enforces exact header name matching.

Diagnosing Mismatched Fields

If two input tables represent the same business entity but utilize divergent column names (e.g., Table A has Customer_ID while Table B has Client_Number), Prep cannot automatically align them. Instead:

1. Prep creates two separate columns in the unioned output.
2. Rows originating from Table A will contain Customer_ID values and Null in Client_Number.
3. Rows originating from Table B will contain Client_Number values and Null in Customer_ID.
4. In the Union Profile pane, Prep visually flags these fields with a distinct color bar and displays an unbalanced distribution with high null percentages.

Resolving Schema Mismatches: Visual Column Merging

To resolve this discrepancy without writing custom SQL or altering source tables:

• Drag and Drop Merge: In the Union Profile pane, click and drag the Client_Number field card directly on top of the Customer_ID field card. Prep immediately collapses both columns into a single unified field.
• Context Menu Merge: Select both fields simultaneously using Ctrl-click (or Cmd-click), right-click, and select Merge Fields.
• Renaming at Source: Alternatively, adding a Clean step prior to the Union step and renaming Client_Number to Customer_ID ensures that Prep automatically matches the columns during the union.

---

Common Exam Traps & Best Practices

• Exam Trap: Union vs. Join Structure: A Union stacks rows (increases row count, maintains or expands columns), whereas a Join combines columns based on a shared key (increases column count, matches related records). Mismatched union fields create unwanted duplicate columns populated with alternating nulls.
• Exam Trap: Forgetting to Rename Pivoted Fields: Leaving default names like Pivot1 Names and Pivot1 Values in published outputs causes confusion for report authors in Desktop. Always rename pivoted fields in the Pivot step or subsequent Clean step.
• Wildcard scope check: Preview the matched file or table list before running the flow. An overly broad pattern can ingest unintended files, while a narrow pattern can omit a new period or region.
• Exam Trap: Delimiter Inconsistencies in Automatic Split: If an address field contains varying numbers of commas (e.g., some include apartment numbers while others do not), Automatic Split will misalign city, state, and zip columns. Use Custom Split with Last N to reliably parse standard trailing fields like State and Zip Code.