Greetings, Excel enthusiasts! I'm Wayne Winston, your go-to guide for mastering Microsoft Excel. In this installment of our series on Excel tips, we'll delve into the power-packed realm of Power Query and unveil the magic of transforming a flat file into an organized, tabular format. So, let's dive in and enhance your accounting and finance prowess with this invaluable Excel skill.
If you missed our last tutorial, we explored how Power Query can effortlessly transform data into a flat table structure, simplifying the management of information. Notably, we demonstrated the seamless integration of new entries into our flat file with a quick refresh. Now, let's take it a step further and learn how to wield Power Query to convert a flat file into an elegant tabular format.
To follow along and practice, you can download the Excel workbook using this link.
Understanding the Flat File
Before we embark on our journey, let's take a glance at our starting point—a flat Excel file, containing data about products sold, the respective sale months, and the corresponding units sold during those months.
Figure 1: A Flat Excel File
Our objective is to leverage the capabilities of Power Query to reorganize this data into a structured table, as showcased in the sample workbook.
Figure 2: Sales in a Table Format
To accomplish this transformation, follow these step-by-step instructions in your Excel workbook:
- Select and Convert to Table:
- Highlight the data in the range C1:E36.
- Press Ctrl + T to convert the selected range into an Excel table.
- Access Power Query:
- Navigate to the Data tab.
- In the Get & Transform Data Group, click From Table/Range. (Note: It might be labeled as “From Sheet” based on your Office 365 version.)
- Open Power Query Editor:
- The Power Query Editor window will emerge.
- Hold down Shift to select both the Attribute and Value columns.
- Pivot the Columns:
- In the Any Column group on the Transform tab, click the Pivot Column button.
- Configure Pivot Column:
- The Pivot Column dialog box appears.
- Keep the default settings unchanged and click OK.
- Sort the Product Column:
- Click the Home table in the Power Query Editor window.
- Select the product column.
- Click the A-Z button in the Sort group on the Home tab.
- Close & Load:
- Return to 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 and Load.
- Test with New Data:
- Add a new entry to your source data (e.g., 25 units of soda in January).
- Click into the table and choose Refresh.
Now, revel in the transformation! Your data is elegantly summarized in a table format, with the Product column meticulously sorted in alphabetical order. And the best part—your new data seamlessly integrates into the table with a mere refresh.
In our upcoming session next month, brace yourself for an advanced Power Query tutorial where we unravel the art of transforming messy, irregular data into a structured and comprehensible format. Until then, keep honing your Excel skills, and feel free to explore more tips and tricks in the Vishal blog for tax professionals, accountants, and CPAs.
Stay tuned for our journey into the depths of Power Query magic!
Note: All images and links in this article are for illustrative purposes and practice, and the Excel workbook can be accessed via the provided link.
Sources:
- Vishal Blog: Excel Tips: Power Query Part III - Creating a table from a flat file
- Microsoft Excel: Power Query Documentation