UnPivot

The Unpivot Transformation is used to transform data from a wide format (where data is spread across multiple columns) into a long format (where data is stacked into rows). Its primary applications include:

1. Data Reshaping: Converts data with multiple columns into a more compact and analyzable format by consolidating column values into rows.
2. Improving Data Analysis: Makes it easier to analyze data, especially when fields represent similar attributes (e.g., monthly sales data across columns, transformed into a row format with a “Month” column and “Sales” column).
3. Normalization: Helps normalize data, ensuring each attribute appears in a single column rather than across multiple columns.
4. Handling Pivoted Data: Useful when working with data that has been pivoted or transposed (e.g., from a report) and needs to be restructured for further analysis or integration.
5. Simplifying Aggregations: Prepares data for aggregation and comparison by turning columns into rows, making it easier to compute sums, averages, or perform other analyses.

This transformation is particularly useful for converting wide datasets into a more standardized format that is easier to process, query, or visualize.

Pivot types

There are two different kinds of Pivot tables:

Multiple columns Pivot

For example, a dataset that lists shops’ sales has one row for each shop, with the month and the sales amount shown in columns in the row.

The following diagram shows a data pivot with multiple columns:

Transformed Data

Single column Pivot

For example, a file that holds a list of products bought by the customer with product codes delimited by semicolon.

The following picture shows a data pivot with a single pivot column.

After the Unpivot transformation normalizes the data set, the data set contains a different row for each product that the customer purchased.

Pivoted Data Example

UnPivoted Data

Configuring the UnPivot Transformation

To change UnPivot properties double click on the object.

Fill in Description and select appropriate Pivot type

Note:

Following actions apply only to multiple fields Pivot

Map Input fields to Outputs and Groups

Result