Power Query is an Excel-based business intelligence application that lets you import data from various sources and then clean, convert, and restructure it as needed for power query training.
It allows you to create a query once and reuse it by refreshing the page. It’s also quite effective. Power Query can input and clean millions of rows into a data model, which can be analyzed. The user interface is intuitive and straightforward, making it simple to learn. Compared to other Excel features like formulae or VBA, it has a very short learning curve.
The best part is that none of it requires you to learn or use any code. Like the Macro recorder in VBA, the power query editor records all of your modifications step by step and translates them to M code for you.
You can undoubtedly alter or write your own M code if you want to, but you don’t have to.
To follow along, get the data used in this post.
What Does Power Query Have to Offer?
Assume you receive a monthly sales report from your system in the form of a text file that looks like this.
Every month, you must go to the folder where the file was posted, open it, and duplicate the contents. The text to column feature is then used to split the input into new columns.
Because the system reports the salesperson’s ID, you’ll need to add a new column to the data and use a VLOOKUP to determine who the salesperson is for each ID. Then you’ll need to total up the sales by salesperson and figure out how much commission you’ll have to payout.
The product ID must also be linked to the product category. However, only the first four digits of the product code are related to the class. You create a new column and use the LEFT function to extract the first four digits of the product code, after which you use a VLOOKUP to get the product category. You can now summarise the information.
It may just take an hour once a month, but it’s a monotonous job that isn’t fun and takes time away from time you should be studying data and providing significant insight.
With Power Query, all of this can be automated down to a monthly refresh button click. All you have to do is create the query once and reuse it every month, saving you an hour of effort!
What is Power Query?
Excel 2010 and 2013 users can download and install Power Query, which will appear as a new tab in the ribbon labeled Power Query. It was renamed Get & Transform in 2016 and no longer appeared on the Data tab.
