Unpivot
The Unpivot transformation component lets you turn your columns of data into rows. This generally means reducing the number of columns and increasing the number of rows.
To configure the Unpivot component, select a number of "Columns to Narrow" that contain data to be flattened into rows. The names of the "Columns to Narrow" will become values in a new column, which you name in the "Output Names Column Name" property, and the values from the "Columns to Narrow" will become values in a new column, which you name in the "Output Values Column Name" property.
This component is equivalent to an SQL UNPIVOT function. The Unpivot component is not a direct reversal of the Pivot component, because it cannot undo the aggregation performed in the Pivot component.
Use case
This component is useful if you need to transform a wide dataset into a long format, which is often more suitable for further analysis. For example, you can use it to transform monthly columns of sales data into a long format with one column for months and another for sales data.
Properties
Name = string
A human-readable name for the component.
Columns to Narrow = dual listbox
Select the columns in the source table to narrow into a single pivot column. The names of these columns will populate one new output column, the values of these columns will populate another new output column. The names of these new columns are dictated by the 'Output Names Column Name' and 'Output Values Column Name' properties.
Output Names Column Name = string
Assign a name to the generated column that will be populated with the names from the columns in the column list.
Output Values Column Name = string
Assign a name to the generated column that will be populated with the values from the columns in the column list.
Examples
Goal: Unpivot monthly sales data into a single column
We have a table of sales data that shows how many of each product type were sold each month. Let's use the Unpivot component to transform the monthly columns into a single Month column.
Input data
| Product | Jan | Feb | Mar |
|---|---|---|---|
| T-Shirt | 150 | 150 | 200 |
| Hoodie | 300 | 430 | |
| Cap | 60 | 90 | 120 |
Component configuration:
- Columns To Narrow: Jan, Feb, Mar
- Output Names Column Name: Month
- Output Values Column Name: Total Sales
Output data
| Product | Month | Total Sales |
|---|---|---|
| T-Shirt | Jan | 150 |
| T-Shirt | Feb | 150 |
| T-Shirt | Mar | 200 |
| Hoodie | Jan | 300 |
| Hoodie | Feb | |
| Hoodie | Mar | 430 |
| Cap | Jan | 60 |
| Cap | Feb | 90 |
| Cap | Mar | 120 |
If you read Example 1 in our Pivot component guide, you will see that although we have unpivoted the data into the original columns from that example, it has not undone the SUM aggregation performed on the sales data. For example, instead of two individual sales of 100 and 50 t-shirts in January, we have a single row with the total sales value 150.
Unpivot scores per subject into a single column
We have a table containing two students' average exam score per subject. Let's use the Unpivot component to transform the individual subject columns into a single column.
Input data
| Student_ID | Science | History | Geography |
|---|---|---|---|
| S001 | 87.5 | 78 | |
| S002 | 89 | 88 |
Component configuration:
- Columns To Narrow: Science, History, Geography
- Output Names Column Name: Subject
- Output Values Column Name: Average Score
Output data
| Student_ID | Subject | Average Score |
|---|---|---|
| S001 | Science | 87.5 |
| S001 | History | 78 |
| S001 | Geography | |
| S002 | Science | |
| S002 | History | 89 |
| S002 | Geography | 88 |
If you read Example 2 in our Pivot component guide, you will see that although we have unpivoted the data into the original columns from that example, it has not undone the AVERAGE aggregation performed on the score data. For example, instead of two individual Science scores of 85 and 90 for student S001, we have a single row with the average score value 87.5.