In the modeller, you use the Unpivot button to convert data from a pivot table format to a list table format.
What’s the difference between a pivot table and a list table?
A pivot table format is two-dimensional with context along rows and columns and values at the intersections.
A list table format is one-dimensional where each row is distinct and contains both context and value.
Why would I unpivot columns in my data source?
The most common use of the unpivot feature is for creating modelled data sources that will be used for metrics. Metrics refer to single columns of data and, as such, require data to be in a list table format.
If the data you want to track in a metric is spread across several columns in your data source, you can use unpivot to consolidate the data into two, single columns - one for “values” and one for “labels”.
See below for a common example, where dates and their associated values are spread across multiple columns:
When you select those columns and unpivot them, they become a single “Labels” column and a single “Values” column. (See below.)
How do I unpivot columns in my data source?
You can either unpivot columns when you originally create and model a data source or from your list of data sources (by selecting the modelled data source you want to modify and clicking Edit Model).
To unpivot columns in a data source:
- With the data source open in the modeller, click the
Unpivot button in the toolbar. - Select the checkboxes for the columns in the data source you want to unpivot and click Apply.
Note: You must select at least two columns.
- The columns you selected are unpivoted into two columns - a Labels column and a Values column.
Note: Your data source now has more rows than before because each value in the unpivoted column now has its own row.
- Rename the columns (optional).
- Click Save or Save and Exit, depending on the next action you want to take with the modelled data source.
Note: You cannot edit formulas for unpivoted columns. You also cannot duplicate or remove them.