Unpivoting data

Unpivoting data is sometimes referred to as flattening the data, which means that all similar values appear in columns instead of rows. For instance, the Profit & Loss By Class report that I exported from QuickBooks Desktop, shown in Figure 12.24, spans 123 columns, running from column A to column DS. It’s hard to do much with a report like that since the data is so broadly dispersed. Let’s use Power Query in Excel for Windows or macOS to transpose 123 columns of data into three. Once again, I’ll move faster by not renaming anything in the Applied Steps list:

Figure 12.24 – Data to be unpivoted

1. Press Ctrl + N ( + N) to create a blank workbook (or choose File | New and choose Blank Workbook).
2. Click Data | Get Data | From File | From Excel Workbook, select Chapter 12 – Unpivoting Data.xlsx, and then click Import.
3. Select Sheet1 from the list and then click Transform Data.
4. Click Column1 and...