Career

Excel Tips: Power Query Part II – Flattening a table

Excel Tips: Power Query Part II – Flattening a table

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

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.

  1. Click into the pivot table.
  2. 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.)
  3. The Power Query Editor window will open. Click the Transform tab.
  4. Select the January-April columns by holding down the Shift key.
  5. In the Any Column group of the Transform tab, click the Unpivot Columns button. This is the step that “flattens” the data!
  6. 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.
  7. 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.
  8. 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).
  9. This second Excel file contains the query results.
 
Figure 2: New entry of candy sales in May appears in the refreshed query

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.

Try Becker's CPA Exam Review FREE for 14 days
FacebookLinkedinXEmail

About the author

Dr. Wayne Winston is a Professor Emeritus of Decision Sciences at the Kelley School of Business at Indiana University. He holds a B.S. in Mathematics from M.I.T. and a PhD in operations research from Yale. He won more than 40 teaching awards at Indiana University. He has written more than a dozen books including "Marketing Analytics", "Data Analysis and Decision Making", "Operations Research", "Practical Management Science", "Office 365 Data Analysis and Business Modeling", and "Mathletics." Dr. Winston has taught classes and consulted for many leading global organizations. He is also a two-time Jeopardy! champion and has consulted for the NBA’s Dallas Mavericks and New York Knicks. His Linkedin class on Forecasting has been completed by over 250,000 learners. He has also published 30 research articles.

Featured

Enrolled Agent vs CPA
Enrolled Agent vs CPA
Read More
The best side hustles for accountants
The best side hustles for accountants
Read More
Acing your accounting job interview
Acing your accounting job interview
Read More

Now Leaving Becker.com

You are leaving the Becker.com website. Once you click “continue,” you will be brought to a third-party website. Please be aware, the privacy policy may differ on the third-party website. Adtalem Global Education is not responsible for the security, contents and accuracy of any information provided on the third-party website. Note that the website may still be a third-party website even the format is similar to the Becker.com website.

Continue