Printer Friendly

Excel: unpivot data using power query.

Pivot tables do an amazing job of creating summary reports as long as your original data is in the correct structure. When your data isn't structured properly, the pivot tables become difficult to use. In Figure 1, for example, the data set includes Region, Market, Model, and Quarter, but the Quarter field stretches across the columns. This is common to see, but it creates a pivot table that's difficult to use.

To create a better pivot table, you need to add a new column with the Quarter number, but then you end up with four times as many records. You're essentially "unpivoting" the quarter field back into the rows to make the data ready for a real pivot table. The manual Sort-Copy-Cut-Paste steps to transform the data are tedious enough when you have to do them four times for quarters, but it's even worse if you have to do them 12 times for months.

Power Query, a new free add-in from Microsoft for Excel 2010 and Excel 2013, can help solve this problem. While it's one component of the "Power Business Intelligence" suite, this part is free and doesn't require you to have a Power Business Intelligence edition of Office in order to use it.

Downloading Power Query

Before you download Power Query, you have to figure out if you are running 32bit or 64-bit Excel. Even on 64-bit computers, Microsoft defaults to the 32-bit installation, so it's worth the two-minute process to check.

For Excel 2010, open Excel and go to File, Help. Look on the right side of the screen under "About Microsoft Excel." You'll see a long string of numbers such as "Version 14.0.7116.5000 (64-bit)." The number in parentheses indicates if you have 32- or 64-bit Excel installed.

For Excel 2013, open Excel and go to File, Account. Click the About Excel button on the right side of the screen. A new box called "About Microsoft Excel" pops up. Your eyes might be drawn to the scrollable box, but you actually have to look above that box in the very first line of text under the title bar. There you'll see a very long set of numbers, such as Microsoft Excel 2013 (15.0.4615.1000) MSO (15.0.4614. 1000) 64-bit. The end of these numbers indicates if you have 32- or 64-bit.

Once you know which version of Excel you have, go to your favorite search engine and search for "Download Power Query." One of the top results (if not the very first one) should be a link to the Microsoft Download Center. Go to that page and click the Download button. You will be asked to choose either the 32-bit or 64-bit version of the add-in. Make sure to choose the version that matches your Excel installation.

Power Query

Say that you have data that you have to import every day. The data might be in a CSV file, or in SQL Server, or on Face book or a Web page. The data is rarely clean-it might require several transformation steps, such as deleting extra columns, splitting a column, or deleting the grand total row. You can use Power Query to do those steps once. Power Query saves the data source location and all of the transformation steps into a single query definition. Tomorrow, you simply open the Excel workbook, click Refresh, and Power Query repeats all of your steps on the current data source.

Using Unpivot Feature

Solving the problem with the data in Figure 1 uses only a tiny portion of Power Query's functionality. The data is already in Excel, so there's no need to import it. To put the data in a format that can be used to create a pivot table, choose a cell in your Excel data. On the Power Query tab, choose From Table in the Excel Data group (see Figure 2). The From Table dialog appears, showing the address of your data table. Make sure the My Table Has Headers box is selected. Click OK.

You're now looking at your data in a special Query Editor window. The Excel ribbon is replaced with new tabs for Home, Transform, Add Column, and View. Switch to the Transform tab. Click on the heading for Q1 to select that column. Select all columns from Q1 to Q4 by holding down the Shift key and clicking on the heading for Q4. On the Transform tab, choose Unpivot Columns (see Figure 3). The four columns are replaced by two new columns: Attribute and Value. You have the option to right-click those headings and rename them, perhaps to "Quarter" and "Revenue."

Finally, go back to the Home tab and choose Apply and Close to return the unpivoted data to a new worksheet in Excel. You now have a data set with four times as many records that can be used to create a pivot table.

If you have Excel 2010 or Excel 2013 and routinely find yourself cleaning data, you'll be amazed at the breadth of tools available in the free Power Query add-in from Microsoft.

Bill Jelen is the coauthor of PowerPivot Alchemy and the host of MrExcel.com. He is a frequent guest speaker at IMA Chapter Professional Development days. Send questions for future articles to IMA@MrExcel.com.

Figure 1

      A            B           C       D       E        F        G

1   Region   Market          Model      Q1      Q2       Q3      Q4
2   West     San Francisco   2500C   E7295   53376    37238    30613
3   South    Austin          2500C   64428   73201    116905   31790
4   East     New York        4500C   43421   125106   123461   31393
COPYRIGHT 2014 Institute of Management Accountants
No portion of this article can be reproduced without the express written permission from the copyright holder.
Copyright 2014 Gale, Cengage Learning. All rights reserved.

Article Details
Printer friendly Cite/link Email Feedback
Title Annotation:TECHNOLOGY; Excel uses Power Query add-in to create pivot table
Author:Jelen, Bill
Publication:Strategic Finance
Geographic Code:1USA
Date:Sep 1, 2014
Words:924
Previous Article:Training is critical for a strong ethical culture.
Next Article:My lifelong learning.
Topics:

Terms of use | Privacy policy | Copyright © 2019 Farlex, Inc. | Feedback | For webmasters