Welcome back to another edition of Dr. Wayne Winston's Excel mastery series! I'm Wayne Winston, the go-to expert on all things Microsoft Excel at Vishal. In this segment, we're about to unravel another invaluable Excel skill that can significantly ease your accounting and finance endeavors.
As accountants, we're no strangers to dealing with disorganized data streaming from various sources. Importing data into Excel from the web or databases can often result in messy, unusable formats. Enter the Power Query tool, a game-changer that can effortlessly transform unruly data into a coherent and usable format. In this article, we'll showcase the diverse data transformation capabilities of Power Query.
Our data conundrum begins with a workbook (Figure 1) containing sales information from a local hardware store:
- Product code and product sold (column G)
- Salesperson and date of transaction (column H)
- Transaction revenue (column I)
Our goal with Power Query is to organize this information, segregating product code, product, salesperson, and date of sale into distinct columns. We also aim to sort transactions first by salesperson and then by product in ascending alphabetical order. Moreover, the process should be dynamic, allowing for a seamless update whenever new data is added. The end result should mirror the cleaned-up data showcased in Figure 2.
Getting Started with Power Query Cleanup
To embark on the cleanup journey, follow these steps:
In the "messy" data file, navigate to the pivot table.
Under the Data tab, in the Get & Transform Data group, choose From Table/Range or From Sheet to initiate the import into the Power Query Editor window.
In the Power Query Editor window, select the Product and Code column.
Within the Transform group on the Home tab, click the Split Column button and opt for "By Number of Characters."
In the ensuing dialog box, set the Number of Characters to 3, and choose "Once, As Far Left as Possible." Click OK.
Rename the new columns - one as Product and the other as Code.
Now, select the Salesperson and Date column.
Again, within the Transform group, click the Split Column button, but this time choose "By Delimiter."
In the Delimiter dialog box, choose Custom and input a hyphen (-). Keep the other settings as default and click OK.
Rename the new columns as Salesperson and Date.
Select the Salesperson column and click the A-Z button in the Sort group of the Home tab. Repeat this step for the Product column.
In the Power Query Editor window’s Home tab, click the Close & Load button or choose Close & Load from the File menu.
Dynamic Data Transformation
To ensure the process accommodates future data additions:
- Add a new row of data for a salesperson named AAA in your original sheet.
- In your new sheet, click Refresh All in the Data tab.
Voila! Your new row of data is seamlessly transformed and appears as the first row of the query result.
This wraps up our Power Query series. I trust I've convinced you of Power Query's prowess in automating tedious tasks, significantly enhancing your productivity in the workplace.
In the dynamic realm of data management, Power Query stands out as a beacon of efficiency. As you continue your journey in Excel mastery, remember that Power Query is your steadfast companion, ready to tackle any data cleanup challenges that come your way. Harness its potential, and watch as it transforms your data woes into streamlined insights and actionable intelligence.
Stay tuned for more insightful updates on mastering Excel and optimizing your accounting and finance workflows. Until next time, happy querying!