Hear from Ken at his session, "Keeping up with Excel" at PD Nexus: Business and Innovation Insights on December 13. Reserve your place today.
On a sunny day in May, I was talking to a new client about training their team to build financial planning and analysis solutions in Excel. “How much are you using Power Query today?” I asked. The phone went silent… they’d never heard of it. Realizing that my client was missing this vital piece of the modern Excel technology stack, I pivoted the conversation to training in Excel fundamentals instead of advanced concepts.
Now don’t get me wrong—we can still build great models in Excel using fundamental concepts. But the truth is that my new client’s team wasn’t ready for advanced Excel training.
If you’re already using Power Query, you’re likely nodding your head in agreement. But if you, like my client, have never heard of this term, I have a simple message for you: Power Query is the most important tool you need to master in Excel today. As I promise every student who takes my “End to Manual Effort” course, Power Query will change your life.
Here are the top six things you should know about Power Query:
-
It’s easy to use—and learn.
Power Query has a simple and intuitive interface that makes it easy to import and clean your data. With just a few clicks, you can transform your data into a format that is easy to analyze and visualize. And you’ll be able to create effective solutions after just a single day of training on the fundamentals.
-
It’s flexible.
Power Query is a versatile tool that can import data from a wide range of data sources and formats. Whether you’re working with Excel spreadsheets, CSV files, text files, PDFs, or databases, Power Query can extract and transform the data into a format that’s easy to work with. Do you need to merge data from multiple sources? Create calculated columns? Filter data based on specific criteria and/or stack the outputs into a single tall table? No problem. Power Query has you covered.
-
It saves you time—twice.
Data preparation is time-consuming, often accounting for 60% to 80% of the time spent on any given analysis task. Power Query’s interface makes it easy to clean your data much more quickly than traditional Excel methods, and it has another massive benefit: A one-click refresh capability that will apply the same steps to your updated data source—which means no programming or extra work for you. (See diagram.) This will save you hours of work and allow you to focus on what you were hired to do: interpreting the data (rather than cleaning it).
-
It lives in the Excel software you already own.
Power Query was first released in 2014 as a free add-in for Microsoft Excel 2010 and 2013. Since 2016, Power Query has been built into every Windows version of Excel, including Office 365. Recently, Microsoft has also started bringing Power Query to the Office 365 release of the Mac version of Excel.
-
It provides quality assurance.
One of the major drawbacks of classic Excel data cleanup is that it enables users to manipulate the original data source without leaving a record of the changes. Power Query changes this by using a read-only approach to get data and by recording every step of the cleansing and reshaping process. Not only do you end up with clean tabular data at the end of this process, but you also receive a fully auditable trail of every change made along the way—again, with no extra effort from you.
-
It lives in more than just Excel.
In addition to acting as a dedicated “data studio” in Excel, Power Query is integrated in many of the products in the Microsoft Power Platform, including other powerful tools such as Power BI and Power Apps. This means that you aren’t learning a skill you’ll only be able to apply to one software package—you’re learning a skill that’s portable to multiple products.
Save yourself time and manual effort
As an accountant who lives and breathes Excel, I cannot imagine doing my work today without Power Query. Every time I see someone copy and paste data into last month’s Excel workbook or update the source range of a PivotTable, I can’t help but think they’re doing unnecessary work that could be automated by Power Query today. This is an indispensable tool that enables us to extract and transform data efficiently while saving us valuable time, improving data accuracy, and allowing us to reuse analyses we’ve already created.
In short, if there’s one tool you need to master in Excel today, Power Query is it.
Author
Ken Puls, FCPA, FCMA, is an expert on self-service business intelligence using Microsoft tools. He is the founder of Excelguru Consulting Inc. and Skillwave.Training and a world-respected speaker, trainer, author, blogger, and community leader who has received the prestigious Microsoft MVP Award every year since 2006. As a recurring instructor for CPABC’s PD Program, he helps CPAs get the most out of Microsoft Excel and Power BI.
This article was originally published in the July/August 2023 issue of CPABC in Focus.
