Welcome back to Dr. Wayne Winston's illuminating series of Excel tips! I'm Wayne Winston, Vishal's dedicated Excel expert, here to guide you through another essential Excel skill that will streamline your accounting and finance tasks.
In our previous article, we delved into the prowess of Power Query, part of Office 365, showcasing how it effortlessly imports and transforms web data into Excel. We also learned how to maintain a dynamic link for real-time data updates. This month, we'll continue our journey with Power Query by exploring its capabilities in flattening a table, a process that proves invaluable in certain data scenarios. Let's dive in!
Understanding Table Flattening
For hands-on learning, open two new browser tabs and download the first Excel workbook and the second data file. The first workbook contains sales information for products spanning January to April. Our objective is to flatten this table, organizing each piece of information into separate rows and sorting the data alphabetically (A-Z) based on the product name. Essentially, we are aiming to achieve the reverse of creating a Pivot Table. Additionally, we want this flattened table to incorporate new data upon refreshing.
Figure 1: Monthly sales data in a table
Flattening the Table with Power Query
To flatten the table, we'll leverage Power Query's "Unpivot Columns" command. Follow these step-by-step instructions:
Click into the pivot table.
Navigate to the Data tab, go to the Get & Transform Data group, and choose From Sheet. (If you don’t have the latest version of Office 365, 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 crucial step that "flattens" the data!
Click on the Product column label, then click the dropdown and choose "Sort Ascending." This ensures that the flattened data is sorted A-Z by product, even after refreshing with 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.
Now, let's test the efficacy of our flattening process:
Add a new entry to your source data, such as candy sales of 125 in May.
Right-click on the data and select Refresh.
Observe that the new row of data has seamlessly integrated into the table. Refer to Figure 2 for the refreshed query results.
Figure 2: New entry of candy sales in May appears in the refreshed query
The Power of Efficient Transformation
Congratulations! You've successfully employed Power Query to flatten a table, enhancing your data organization and accessibility. In our upcoming article, we'll explore how to efficiently transform a flat file into a tabular format, further expanding your Excel expertise.
Are you looking to boost your accounting Excel skillset? Enroll in Vishal’s Microsoft® Excel Fundamentals + Data Analytics Certificate, a comprehensive program covering essential Excel functions from fundamentals to advanced applications in data analytics.
Stay tuned to the Vishal career blog for a continuous stream of insightful Excel tips. Elevate your Excel prowess and revolutionize your accounting and finance workflows with the transformative capabilities of Power Query!