Welcome to another entry in our series of Dr. Wayne Winston’s best Excel tips! I’m Wayne Winston, Becker’s resident expert on all things Microsoft Excel. Let’s learn another helpful Excel skill that can make your accounting and finance work a bit easier.
Last month we showed you how to use Office 365’s Power Query to import and transform data from the web into Excel, and how to keep your spreadsheet refreshed for the most updated data. This month, we’ll show you how Power Query can ease the task of flattening a table. Let’s get started!
Flattening a Table
To start, copy and paste the link to this file and the link to this second data file into new browser tabs to download two Excel workbooks and follow along.
The first workbook contains sales information for products during the months January-April. We want to flatten the table so that each piece of information is in a separate row and that the data is sorted alphabetically (A-Z) based on product name – essentially, the opposite function of creating a Pivot Table. Upon refreshing, we also want the flattened table to include new data.
Figure 1: Monthly sales data in a table
To flatten the table, you will need to use Power Query’s Unpivot Columns command. Follow these steps.
- Click into the pivot table.
- From the data tab, go to the Get & Transform Data group and choose From Sheet (if you do not have the latest version of Office 365, you can select Data --> from Table / Range.)
- The Power Query Editor window will open. Click the Transform tab.
- Select the January-April columns by holding down the Shift key.
- In the Any Column group of the Transform tab, click the Unpivot Columns button. This is the step that “flattens” the data!
- Click on the Product column label, and click the dropdown and choose Sort Ascending. This step ensures that your flattened data will be sorted A-Z by product, even when we refresh after adding new data.
- In the Power Query Editor window’s Home tab, click the Close & Load button in the Close group. Alternatively, open the File menu and choose Close & Load.
- Add a new entry to your source data, for candy sales of 125 in May. After right-clicking on the data and selecting Refresh, you will see that the new row of data has been correctly added to the table (see Figure 2).
- This second Excel file contains the query results.
Figure 2: New entry of candy sales in May appears in the refreshed query
Next month, we will show you how to efficiently transform a flat file into a tabular format.
Want to improve your accounting Excel skillset? Enroll in Becker’s Microsoft® Excel Fundamentals + Data Analytics Certificate and learn essential Excel functions for accounting, from fundamentals all the way to in-depth applications for data analytics.
Keep visiting the Becker career blog for more helpful Excel tips.
