View Source

Reshapes the data by merging one or more columns into key and value columns. Keys are the names of input columns, and value columns are the cell values from the source.

Rows of data are duplicated, once for each input column.

The unpivot column can be applied to multiple columns in the same transform. All columns are un-pivoted into the same key and value columns. When this transform is applied to two columns, the number of rows in the dataset is doubled.

This transform is the opposite of the pivot transform, which converts a set of column values into distinct columns. See Pivot Transform .

Single- or multi-column example:

You can specify single columns or comma-separated sets of columns.

unpivot col: FirstName, MiddleInitial

Output: Converts the values in the columns FirstName and MiddleInitial into separate key and value columns.

Column range example:

You can also specify ranges of columns using the tilde (~) operator:

unpivot col:Column1~Column20

Output: Converts all of the values in columns between Column1 and Column20 into key and value columns.

unpivot col: column_ref [groupEvery: int_num]

Token	Required?	Data Type	Description
unpivot	Y	transform	Name of the transform
col	Y	string	Name of source column or columns
groupEvery	N	string	If specified, this parameter defines the number of individual key-value pairs to store in each generated column. Default is `1`.

col

Identifies the column or columns to which to apply the transform. You can specify one or more columns.

Column ranges are supported:
myColumn1~myColumn5

NOTE: For the col value, you can use the asterisk ( * ) wildcard 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.

Required?	Data Type
Yes	String (column name)

groupEvery

Required?	Data Type
No	Integer (positive)

Example - Basic Unpivot

Source:

productName	productColor	productSize
Whizbang	red	M
Whizbang	red, blue	L
Whizbang	green	M
Bangwhiz	red	S
Bangwhiz	blue	M
Bangwhiz	red	S

Tranformation:

After you have created a header, if necessary, add the following transformation:

Results:

productName	productSize	key	value
Whizbang	M	productColor	red
Whizbang	L	productColor	red, blue
Whizbang	M	productColor	green
Bangwhiz	S	productColor	red
Bangwhiz	M	productColor	blue
Bangwhiz	S	productColor	red

Extended:

Note how each instance of a value results in a separate row; duplicate values are included. For a single-column unpivot, this transform results in the same number of rows as the source.

Since the value is treated as a string, the value red, blue is treated as one value.

Now, edit the transformation you just added. Replace it with the following, which includes the productSize key as part of the transformation:

Results:

productName	key	value
Whizbang	productColor	red
Whizbang	productSize	M
Whizbang	productColor	red, blue
Whizbang	productSize	L
Whizbang	productColor	green
Whizbang	productSize	M
Bangwhiz	productColor	red
Bangwhiz	productSize	S
Bangwhiz	productColor	blue
Bangwhiz	productSize	M
Bangwhiz	productColor	red
Bangwhiz	productSize	S

Row keys alternate based on the order in which the source columns are specified in the transform. Since the transform specifies two columns, the number of key-value pairs is doubled, which results in a dataset that has twice as many rows as the source.

Example - Basic Pivot with groupEvery

Tranformation:

From the previous example, modify the unpivot transform to be the following:

Results:

productName	key1	value1	key2	value2
Whizbang	productColor	red	productSize	M
Whizbang	productColor	red, blue	productSize	L
Whizbang	productColor	green	productSize	M
Bangwhiz	productColor	red	productSize	S
Bangwhiz	productColor	blue	productSize	M
Bangwhiz	productColor	red	productSize	S