Excel: unpivot data using power query.
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.
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
|Printer friendly Cite/link Email Feedback|
|Title Annotation:||TECHNOLOGY; Excel uses Power Query add-in to create pivot table|
|Date:||Sep 1, 2014|
|Previous Article:||Training is critical for a strong ethical culture.|
|Next Article:||My lifelong learning.|