What is Power Query?
Power Query is a tool in Microsoft Excel that simplifies the process of importing data from different source files and sorting them into an Excel sheet in the most convenient and usable format.
It also eliminates the need to execute the same filtering techniques to transform the same data set at different points in time; the user only needs to set up a query, that is, the rules for sorting, once, and refresh the query every time the action is to be repeated.
Power Query is a user-friendly business intelligence tool that does not require the user to learn any specific code. First made available in MS Excel in 2010, the feature is a free add-in in Excel 2010 and 2013. Since 2016, it’s been completely integrated into Excel.
- Power Query is a tool in Microsoft Excel that simplifies the process of importing data from different source files and sorting them into an Excel sheet in the most convenient and usable format.
- Power Query is a user-friendly business intelligence tool that does not require the user to learn any specific code.
- The biggest advantage offered by the Power Query tool is higher efficiency; using Excel with Power Query brings the user faster results in a very short time.
A user wants to import sales data from a text file into an Excel sheet to calculate the monthly commission payable to each salesperson. The data in the text file is as follows:
Employee ID, Product ID, Date, Commission Rate, Units Sold, Per Unit Price
A10001, 10200301, 2020-01-30, 20.0%, 65, 100
A10002, 10200304, 2020-01-30, 20.0%, 77, 200
A10003, 10200301, 2020-01-30, 20.0%, 50, 100
A10002, 10200301, 2020-01-30, 20.0%, 44, 100
A10003, 10200304, 2020-01-30, 20.0%, 60, 200
A10001, 10200304, 2020-01-30, 20.0%, 35, 200
After copying the above data into Excel using the text to column option, the data will look as follows:
To calculate the amount to be paid to each salesperson:
- Find the name of the employee associated with each ID using VLOOKUP.
- Associate each product ID with the name of the product.
- Use mathematical formulas to calculate the total amount of sales and commission payable.
After adding and removing some columns, the final data must look as follows:
This exercise is not difficult but will take the user some time. If the same sorting and filtering must be done on a regular basis, it gets mundane.
With the Power Query tool, the user only needs to perform the exercise once. The instructions that were used to arrive at the final data will be stored as a query, and every month, the same data can be generated by choosing the refresh option on the query thus set up.
To learn more, check out CFI’s Power Query Fundamentals Course!
Other Uses of Power Query
Clearly, the biggest advantage offered by the Power Query tool is greater efficiency. Excel itself is a useful and efficient tool for data formatting and analysis; using Excel with Power Query brings the user faster results.
The following features of the tool make it even more useful for regular applications:
1. Connection to Various Data Sources
Power Query is programmed to export data from several sources, including, but not restricted to, text files, Excel workbooks, and CSV files.
Once set up, a query is stored in the system, and every time data needs to be imported and formatted in a similar manner, the instructions can be repeated using the refresh option. It eliminates the need for repetitive manual work and makes the process more efficient.
2. Combining Tables
When additions to the same source data have to be imported at regular time intervals, say, every week or month, it becomes difficult to manually ensure the correct replication of data.
In the example above, if a single report needs to be made on the amount of revenue earned by each salesperson over one year, data from each month should be added to that from the previous months, and figures need to be correctly added up. It becomes very difficult to accomplish manually if there are many employees in the company.
Power Query provides the option of combining different data sets. The sales reports from each month can be combined with that of the previous months by specifying the necessary instructions.
3. Merging Tables
The Merge option in Power Query substitutes the VLOOKUP function in Excel. The latter is indeed a useful function to look up corresponding values, but it becomes slightly inconvenient to use it on a large dataset spanning several thousands of rows.
In the example above, if there are different tables showing the sales of different products by each salesperson, these tables can be merged using Power Query to show the total sales achieved by each employee. Regardless of the size of the data, it will not affect the speed of execution.
To keep learning and developing your knowledge of business intelligence and data analysis, we highly recommend the additional resources below: