Unpivoting columns

Data can be organized in different ways, for example, in a short/wide or tall/skinny format, but still contain the same information. Often, it is easier to visualize data organized in a tall/skinny format, that is, when the values are collected in just a few value columns. Unpivoting is one way to transform data from a short/wide to a tall/skinny format, so the data can be presented the way you want it in the visualizations.

You can select several columns with similar values and combine them into a single column which includes all values, using Unpivot. If the data types of source columns should differ, the varying data is converted to a common data type in the combined column.

Prerequisites

You must have some data loaded in the analysis, and the analysis must be in Editing mode.

Procedure

  1. On the authoring bar, click Data in analysis and click to select the columns to combine.
    • To select a consecutive group of columns, click the first column, press Shift, and then click the last column.
    • To select non-consecutive columns, press Ctrl, and then click each separate column, or, make sure to select the check box for each column.
  2. Right-click on one of the chosen columns and select Unpivot from the pop-up menu.

Result

The values from the selected columns are combined into a single column called "Value". Also, a new category column is created, where the previous column names are used as categories.

Example: Combining multiple temperature inputs to an average temperature

This example shows unpivoting of a very simple data table. In the original data table, there are three columns and four rows. Each row contains a city, a morning temperature and an evening temperature for each city:

Short/wide data table with temperature data.

While this is certainly useful data, you may want to determine the average temperature of all the cities for the whole day instead.

In the Data in analysis flyout, click to select the Morning temperature and Evening temperature columns, then right-click and select Unpivot.

Unpivot option in the pop-up menu of the Data panel.

You can change the names of the new columns in the flyout, if desired. In this example, the name of the new value column is changed to "Temperature", and the new category column is called "Type of measurement".

Editing a column name in the expanded Data panel.

After unpivoting the data, there is one row for each measurement in the data table.

Tall/skinny data table with temperature data, after unpivoting.

Now, you can easily show an average value of the temperature in each city, for example, in a bar chart.

Bar chart showing average temperature in different cities.

Note: In the original data, the morning temperatures were given as integers and the evening temperatures as real numbers. In the unpivoted data table, all values must be of the same data type. Therefore, the integers were automatically changed to real numbers (because changing the real number temperatures into integers would have resulted in a loss of information).

Example: Combining sales data from several desks into a single column

In this example, a larger data set containing data on the sales of entrance tickets for a museum is used. The original data table shows data for each of the five ticket counters (desks) and the number of tickets they have sold to adults, children and senior citizens each day. The data is organized in a short/wide format, that is, the spreadsheet has many columns with similar data.

Short/wide data table with ticket sales information.

If you are more interested in analyzing ticket sales in general, rather than needing to know which counter sold how many tickets to whom, you can unpivot the data. This way, you can combine the Desk columns into a single column, and merge all ticket sales numbers to another column.

In the data table below, the same data is organized in the tall/skinny format. The values from the desks have been combined in one column, called "Tickets", and "Desk" is the new category column showing from which desk the tickets were sold.

Tall/skinny data table with ticket sales information, after unpivoting.

By doing an analysis of the new data table, you can show that Wednesdays are the days when we sell the least amount of tickets.

Bar charts showing ticket sales per week day and quarter.