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 #1
Convert the matrix table, below, into a standard data table. Unpivot all column except "Country".
Before (source table)
Table 1: Winter Olympics medals
| Country | Winter 2010 | Winter 2014 |
|---|---|---|
| Canada | 26 | 25 |
| Norway | 23 | 26 |
| Germany | 30 | 19 |
After (result table)
| Country | Labels | Data |
|---|---|---|
| Canada | Winter 2010 | 26 |
| Norway | Winter 2010 | 23 |
| Germany | Winter 2010 | 30 |
| Canada | Winter 2014 | 25 |
| Norway | Winter 2014 | 26 |
| Germany | Winter 2014 | 19 |
Action parameters
Column name for labels: Labels
Column name for data: Data
Unpivot all columns except these: Country
Example #2
Convert the matrix table, below, into a standard data table.
Before (source table)
Table 1: Winter Olympics medals
| Country | Winter 2010 | Winter 2014 |
|---|---|---|
| Canada | 26 | 25 |
| Norway | 23 | 26 |
| Germany | 30 | 19 |
After (result table)
| Labels | Data |
|---|---|
| Country | Canada |
| Country | Norway |
| Country | Germany |
| Winter 2010 | 26 |
| Winter 2010 | 37 |
| Winter 2010 | 30 |
| Winter 2014 | 25 |
| Winter 2014 | 28 |
| Winter 2014 | 19 |
Action parameters
Column name for labels: Labels
Column name for data: Data
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)