Harness the power of Power Query in Microsoft Excel with no coding for your repetitive tasks
If you find yourself repeating the same steps in Microsoft Excel, Power Query can give you back a substantial amount of time
When you think of automating tasks, generating reports as well as performing several other functions beyond creating and organising spreadsheets, you may well realize that the supreme, mighty Visual Basic for Applications (VBA) has been around for almost 13 years and undoubtly, it has provided excellent business solutions. It has got innumerable advantages especially considering Excel is embraced by the vast majority of the users in business world. I even published a post about how to use VBA to tackle boring tasks. However, to start with, you have to know how to code in Visual Basic language. Other users must have their own copies to execute your handsome VBA programme. When things go wrong, which is quite likely in VBA, your technical support skills are welcomed in the world of debugging. Besides, compatibility issues between different versions of Excel will bring you some “entertaining” challenges.
Now that I am done with denigrating VBA, we can move to Power Query, a powerful data transformation and preparation engine.
To start with, Power Query enables you to extract data from different resources; workbooks, text files, databases, online services, web, Azure, etc.
You can find amazing functions under “Transform” and “Add Column” tabs for efficient data wrangling. Comprehensive calculations thanks to Power Query M Formula Language.
Also, the steps that you apply are saved, which is an excellent feature when it comes to task automation.
In my example, I am creating a modified report based on a daily report by joining a column from another excel sheet as well as changing column names and reordering them. The contents of the daily report are changing due to different figures but the task is exactly the same with no added value and development. Moreover, as it is a manual task, it is particularly prone to error.
Below I show how the report is transformed and my output looks:
Voilà ! As you can see, columns such as C, D, E and F have been changed. While column C joins the report from the other source, column D is reordered and column E is renamed (“Future” previously”).
But how can you generate the report if the daily figures are changing ? It is so simple. You just need to replace the sources by keeping the identical names and hit the “Refresh all” button (Ctrl + Alt + F5 if you fancy) under “Data” tab.
You might hear people say that it only takes five or ten minutes for them to create such a report and they don’t mind; well, until they see the butterfly effect with a simple math. Let’s assume that they create this report 260 times a year and they do it flawlessly (quite realistic, right?). 260 times 5 minutes is amounted to 21 hours, which is almost three working days a year!
Take a deep breath now, think of your daily routine and try to calculate how much time you could save at work, increase your and your collegaues’ productivity if you knew these simple features that require zero coding.
I am looking forward to your comments.
