Skip to main content

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.

注記

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.

注記

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

Unpivot columns

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

Unpivot columns

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

注記

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.