In a technologically driven world, efficiency is at the forefront of data needs to drive decision-making. According to Microsoft, on average, 80 percent of time is spent on preparing data for business needs. While many use Excel daily, a lesser-known tool, Power Query, is invaluable for data transformation and analysis, and it’s becoming increasingly popular among Excel users.
Power Query is an Excel add-in and powerful data transformation tool that allows the user to easily extract, transform, and load data from various sources, making ETL processes much easier in Excel. There are two main steps to using Power Query: loading the data and then transfiguring the data to be in the optimal form for analysis, which is why the Power Query tool is also called “Get and Transform”.
Step 1: Get the Data
First, you need to retrieve the data you want to interpret (“get” the data). There are ample options for where to load data from, with a common source being a table in an Excel spreadsheet. Combining data from multiple sources is where the power of this tool lies. The ability to merge data sets automatically, instead of manually through lookup formulas or other techniques, saves tremendous time from the start of the process and greatly decreases the possibility of human error.
Step 2: Transform the Data
The next step is to utilize the wide range of features to clean and remodel (“transform”) the data. While transformations are made, they are saved in a side panel of the interface. This allows users to replicate changes, undo steps, and remove the guesswork from the transformation process. An added benefit to this step is the limited use of code, which is written automatically in the background and saves time for the user.
So how can Power Query help with the daily work currently being done in Excel? Let’s look at two examples of how Power Query can create efficiencies and allow for more time.
Client Case Study #1:
There was recently an urgent task to take a dataset consisting of thousands of rows of names and break them into separate columns of first name, middle name, last name, prefix, and suffix. However, there was little uniformity in the way the names were written in the dataset. This would have been impossible to complete manually in a timely manner. While Excel could have worked and gotten the job done, it would have been much more tedious and time-consuming. In Power Query, the names were grouped by the number of words and then further grouped by the format the names were written. Then the transformations could be made by splitting the column and cleaning the data. Even if the client provided an additional dataset of names, the applied steps were reused, thus saving time in the process of transforming. (See images below.)
Client Case Study #2:
A client provided data in a spreadsheet and was trying to combine two tables through the use of lookups, followed by manual checks. The first of the two tables had data for accounts within the United States and the second had data for international accounts. The second table contained all the US accounts and its own distinct accounts. Power Query loaded both original tables and was able to create a new table through its merging function, combining the existing shared accounts where necessary. With a few added steps of joining the account names into a unified column and cleaning the data, the majority of the effort in combining these tables was complete. These steps were accomplished without any code and the merge function greatly reduced the risk of error.
With Power Query, you can quickly create clean data sets that can be used in Excel, Power BI, and other analysis software. It offers efficiency and versatility in data handling and analysis, enabling accounting professionals to quickly and easily transform data into valuable insights to drive business decisions. Power Query is similar to Excel in that anyone can learn and use its features, but the two differ in terms of efficiency and the possibilities for analysis. By adding the feature of Power Query, anyone is capable of mastering their own data.