Unpivot Columns
You can convert columns into rows of values. A conversion transformations extracts the values from a specified column or columns and turns the column name and each extracted value into key-value pairs.
Unpivot can be applied to one or more columns.
Often, this transformation is applied to datasets containing pivoted or aggregated data.
Nota
Depending on the number of source columns, an unpivot operation can significantly increase the number of rows in your dataset.
Single-column Unpivot
When you unpivot a single column of data, the column is separated into two new columns in your dataset:
New column name | Values |
|---|---|
key | All values are the name of the source column. |
value | Each row contains one of the row values from the source column. |
Nota
These columns replace the source column in the dataset. To retain the source column, create a copy of it first and then unpivot the copied column.
Source:
The following example contains a very simple set of data:
Name | favoriteColor | favoriteDessert |
|---|---|---|
Anna | red | ice cream |
Bella | pink | cookies |
Callie | blue | pie |
Transformation:
You can unpivot these columns one-by-one into row data:
Transformation Name | |
|---|---|
Parameter: Columns | favoriteColor |
Parameter: Group size | 1 |
Results:
The new unpivoted columns are placed at the end of the dataset, and the source column is removed.
Name | favoriteDessert | key | value |
|---|---|---|---|
Anna | ice cream | favoriteColor | red |
Bella | cookies | favoriteColor | pink |
Callie | pie | favoriteColor | blue |
Multi-column Unpivot
This example turns the data from multiple columns into a single set of key-value pairs, where the key is the column name associated with the source of the data in the value column.
Source:
The following dataset shows student test scores per test. Each row represents the scores of individual students.
StudentId | test1Score | test2Score | test3Score |
|---|---|---|---|
001 | 75 | 79 | 77 |
002 | 84 | 81 | 86 |
003 | 79 | 82 | 87 |
004 | 92 | 94 | 92 |
Transformation:
You can use the following transformation to turn the dataset into one row per student-test combination:
Transformation Name | |
|---|---|
Parameter: Columns | test1Score, test2Score, test3Score |
Parameter: Group size | 1 |
Results:
The results are as follows:
StudentId | key | value |
|---|---|---|
001 | test1Score | 75 |
002 | test2Score | 79 |
003 | test3Score | 77 |
001 | test1Score | 84 |
002 | test2Score | 81 |
003 | test3Score | 86 |
001 | test1Score | 79 |
002 | test2Score | 82 |
003 | test3Score | 87 |
001 | test1Score | 92 |
002 | test2Score | 94 |
003 | test3Score | 92 |
You can then rename the key and value columns as needed.
Ranges
You can specify a range of columns in your dataset. In the previous example, you can specify the three test score columns using the following value in the Columns textbox:
test1Score~test3Score
All three columns are unpivoted.
Wildcards
Nota
You can use the asterisk ( * ) wildcard in the Columns textbox to apply the unpivot to the entire dataset, which generates a key and a value column, containing all column-row entries from the source columns. However, unpivoting a large number of columns can significantly increase the number of rows in your dataset.