This week’s special guest is Ken Puls. He’s a Certified Management Accountant who also runs ExcelGuru.ca. His recent accomplishments include a book on Power Query titled “M is for (Data) Monkey” and a video training endeavor “Power Query Training” which offers an online workshop for first-time Excel users and a digital version of the book.
Let’s get to the details right away!
1 – What is Power Query?
Power Query is a free Excel add-in (for Excel 2010 and 2013) used to primarily import data from databases or text files and clean it up faster. The best feature of this tool is that it automates this entire process of fetching and cleaning up the data, so you have to do it only once in your life! Pretty amazing, right?
In Excel 2016, this tool is a built-in feature called ‘Get & Transform’ and can be found under the Data Ribbon.
2 – What is the book about?
The book uses a natural progression to teach this piece of software, employing real-life data sourced from real-life problems. The aim is to teach people how to deal with commonly faced problems. The book starts with basic transformations, like appending data and dealing with date formats, and ramps it up to teaching how to leverage the programming running behind Power Query.
3 – What is M?
M is the programming language working behind the scenes in Power Query. It is analogous to what is VBA to Excel. The first two-thirds of the book focus on using the User Interface to meet all user needs, as much as is possible. It is not until the last third of the book that M is explored. At the very basic level, it involves putting in formulas, somewhat similar to how Excel functions work. However, like Excel and VBA, it is possible to use Power Query without having the faintest idea of what M is.
4 – End User Adoption
Power Query, like PowerPivot, has had a very slow adoption rate. But Ken believes that Power Query will soon eclipse PowerPivot due to its utility and an excellently designed User Interface.
What’s next?
If Power Query has piqued your interest, the next logical step is to figure out how it can make your life easier. Go on and explore this add-in. Get your colleagues and friends onboard as well.
And do not forget to share your thoughts with us in the comments section below.
- SSSVEDA DAY 7 – Every Team Needs Someone Who Understands Data - February 18, 2018
- SSSVEDA DAY 5 – When Data Analysis is Wrong - October 31, 2017
- SSSVEDA DAY 4 – Sharing the Excel Knowledge - July 18, 2017
