Table of Contents
UNPIVOT
Category: Transform / Advanced
Description
Converts a matrix table into a straight table by rotating columns into "Labels" and "Data" columns. This action is opposite to the Pivot action.
Use cases
This action is used to convert a cross-table (or matrix) into a straight table. It can be used, for instance, to convert a cross-table from an Excel spreadsheet into a table suitable for exporting into a database.
Action settings
| Setting | Description |
|---|---|
| Column name for labels | Enter a name for the column that will contain the column header labels from the matrix table. |
| Column name for data | Enter a name for the column that will contain the data values from the matrix table. |
| Unpivot all columns except these | Select fields that will not be unpivoted, but instead will be repeated down rows, as is, once for each column heading in the matrix table. |
Remarks
Typically, any columns used for row labels in the source matrix table will be selected under "Unpivot all columns except these" to retain those values as row values in the unpivoted table. Otherwise, those values will be listed in the result table with a "label" equal to the matrix table's column heading for the row labels, and data values equal to the row labels.
Examples
Example: Convert the matrix table, below, into a standard data table.
Source table: Winter Olympics medals
| Country | winter 2010 | winter 2014 |
|---|---|---|
| Canada | 26 | 25 |
| United States | 37 | 28 |
| Norway | 23 | 26 |
| Germany | 30 | 19 |
Action parameters:
Column name for labels is "Labels"
Column name for data is "Data"
Unpivot all columns except these = "Country" selected
Result table:
| Country | Labels | Data |
|---|---|---|
| Canada | winter 2010 | 26 |
| United States | winter 2010 | 37 |
| Norway | winter 2010 | 23 |
| Germany | winter 2010 | 30 |
| Canada | winter 2014 | 25 |
| United States | winter 2014 | 28 |
| Norway | winter 2014 | 26 |
| Germany | winter 2014 | 19 |
Notice how the original set of Country values repeat for each value in Labels.
If no columns were selected under "Unpivot all columns except these", the resultant table would appear as below:
Result table:
| Labels | Data |
|---|---|
| Country | Canada |
| Country | United States |
| Country | Norway |
| Country | Germany |
| winter 2010 | 26 |
| winter 2010 | 37 |
| winter 2010 | 23 |
| winter 2010 | 30 |
| winter 2014 | 25 |
| winter 2014 | 28 |
| winter 2014 | 26 |
| winter 2014 | 19 |
Community examples
- Example: Constructing JSON (Project; Module: Table2json; Group: Tab 1; Table: Create JSON elements; Action position: 4)
- Pivot tables basis data (Project; Module: Main; Group: Tab 1; Table: Table 1; Action position: 3)
- Aggregate for +500 columns (Project; Module: Main; Group: Order preserved; Table: Book1.xlsx(2); Action position: 2)
- Duplicating rows based on a condition (Project; Module: Main; Group: Tab 1; Table: Table 1 (2); Action position: 2)
- “Merge another table” action - merge all columns (independently of the column name) (Project; Module: Merge; Group: Merge;
Table: Type + Range; Action position: 2)