Part V: Understanding Power Pivot and Power Query
Power Pivot offers an easy way to unpivot and pivot columns, allowing you to convert
matrix-style tables to tabular data sets (and vice versa) quickly.
The Unpivot Columns command lets you select a set of columns and convert those columns
into two columns: one column consisting of the column labels and another containing the
column data.
You can follow along with this example by downloading the UnpivotExample.xlsx sample file at
http://www.wiley.com/go/excel2019bible.
For instance, in Figure 39.19, the month columns can be unpivoted by selecting the months
and then right-clicking and selecting the Unpivot Columns command.
Figure 39.20 shows the resulting table. Note that the month labels are now an entry in a
new column called Attribute. The month values are now in a new column called Value. You
can, of course, rename these columns to something like Month and Revenue.
FIGURE 39.20
All months are now in a tabular format.
Unpivoting other columns
As nice as the Unpivot Columns command is, there is a flaw. The flaw is that you have to
explicitly select the months you want unpivoted. But what if the number of columns is ever
growing? What if you unpivot January through June, but next month, a new data set will
arrive with July and then August and then September? Because the Unpivot Columns com-
mand forces you essentially to hard-code the columns you want unpivoted, you’ll have to
redo the unpivot each and every month—that is, unless you use the Unpivot Other Columns
