| D toc |
|---|
| D s transforms |
|---|
| Excerpt |
|---|
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 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 the pivot transform transform, which converts a set of column values into distinct columns. See See Pivot Transform .
D s snippet Basic
Single- or multi-column example:
You can specify single columns or comma-separated sets of columns.
| D code |
|---|
unpivot col: FirstName, MiddleInitial |
Output: Converts Converts the values in the columns columns FirstName and and MiddleInitial into separate into separate key and and value columns columns.
Column range example:
You can also specify ranges of columns using the tilde (~) operator:
| D code |
|---|
unpivot col:Column1~Column20 |
Output: Converts all of the values in columns between between Column1 and and Column20 into into key and and value columns columns.
| D s | ||
|---|---|---|
|
| D code |
|---|
| 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. |
| D s lang notes |
|---|
col
Identifies the column or columns to which to apply the transform. You can specify one or more columns.
| Include Page | ||||
|---|---|---|---|---|
|
Column ranges are supported:
d-lang-syntax myColumn1~myColumn5
| Info |
|---|
NOTE: For the |
| d-s | ||
|---|---|---|
|
| Required? | Data Type |
|---|---|
| Yes | String (column name) |
...
| productName | productColor | productSize |
|---|---|---|
| Whizbang | red | M |
| Whizbang | red, blue | L |
| Whizbang | green | M |
| Bangwhiz | red | S |
| Bangwhiz | blue | M |
| Bangwhiz | red | S |
TranformTranformation:
After you have created a header, if necessary, add the following transformtransformation:
| d- |
|---|
...
| trans | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
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 |
...
Note how each instance of a value results in a separate row; duplicate values are included. For a single-column column unpivot, this transform results in the same number of rows as the source.
- Since the value is treated as a string, the value value
red, blueis treated as one value.
Now, edit the transform transformation you just added. Replace it with the following, which includes the the productSize key as part of the transformtransformation:
| d- |
|---|
...
| trans | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
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 the unpivot transform to be the following:
d-
...
trans RawWrangle true Type step WrangleText unpivot col:productColor,productSize groupEvery:2 p01Name Columns p01Value productColor,productSize p02Name Group size p02Value 2 SearchTerm Unpivot columns
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 |
| D s also | ||
|---|---|---|
|
