Depivot
The Depivot() function converts a block of data with row and column headers into an array of columns. In the output, the columns are ordered as follows: column headers first, followed by row headers, and then the value columns. This format is useful for performing calculations on the data.
Related functions: Pivot
Syntax:
Qx.Depivot(Range, Columns, Rows)- In Excel with Quintessence addin versions prior to 25.1, use:
Depivot() - In the Quintessence Editor (Studio) using Quintessence language, use:
Depivot() - In the Quintessence Editor (Studio) using Python language, use:
Qx.Depivot()
ⓘ See parameter descriptions
| Range | The range of cells to transform. |
| Columns (optional) | The columns that contain the key values (column headers). |
| Rows (optional) | The rows that contain the key values (row headers). |
Remarks:
- Unless changed by an administrator, the default parameters are:
- Columns:
C1 - Rows:
1
- Columns:
Examples:
The examples provided below are based on fictitious data for illustrative purposes only.
> Example 1: Depivoting a range of data with headers into three columns
Source Data (range in Excel: A1:C5):
| Factor 1 | Factor 2 | |
| QGW | 3.79 | 3.88 |
| QJU | 6.98 | 6.98 |
| XDU | 5.55 | 5.77 |
| VRR | 6.17 | 0.95 |
Formula:
=Qx.Depivot(A1:C5, "C1", 1) Result:
| QGW | Factor 1 | 3.79 |
| QGW | Factor 2 | 3.88 |
| QJU | Factor 1 | 6.98 |
| QJU | Factor 2 | 6.98 |
| XDU | Factor 1 | 5.55 |
| XDU | Factor 2 | 5.77 |
| VRR | Factor 1 | 6.17 |
| VRR | Factor 2 | 0.95 |
> Example 2: Depivoting a range of data with multiple row and column headers
Source Data (range in Excel: A1:F5):
| Factor 1 | Factor 1 | Factor 2 | Factor 2 | ||
| 2017 | 2018 | 2017 | 2018 | ||
| Stock 1 | QGW | 40 | 45 | 200 | 190 |
| Stock 2 | QJU | 88 | 80 | 67 | 60 |
| Stock 3 | XDU | 55 | 50 | 70 | 75 |
Formula:
=Qx.Depivot(A1:F5, "C1,C2", 2)Result:
| Stock 1 | QGW | Factor 1 | 2017 | 40 |
| Stock 1 | QGW | Factor 1 | 2018 | 45 |
| Stock 1 | QGW | Factor 2 | 2017 | 200 |
| Stock 1 | QGW | Factor 2 | 2018 | 190 |
| Stock 2 | QJU | Factor 1 | 2017 | 88 |
| Stock 2 | QJU | Factor 1 | 2018 | 80 |
| Stock 2 | QJU | Factor 2 | 2017 | 67 |
| Stock 2 | QJU | Factor 2 | 2018 | 60 |
| Stock 3 | XDU | Factor 1 | 2017 | 55 |
| Stock 3 | XDU | Factor 1 | 2018 | 50 |
| Stock 3 | XDU | Factor 2 | 2017 | 70 |
| Stock 3 | XDU | Factor 2 | 2018 | 75 |