Power Queries: Why You Need Them
Those of us in the project controls and earned value management (EVM) world are no stranger to Excel. We’re deeply familiar with spreadsheets that span thousands of lines; our complex projects come with reams of data that must be sorted and collected. You probably have familiar formulas you rely on to calculate data within those gigantic sheets and maybe even find yourself searching through sheets for a specific tidbit of information you need. But there’s a better, sanity-saving option: power queries.
Anyone who utilizes large amounts of data and needs to merge, simplify, and transform data to analyze (and do it all again the following month) can benefit from using power queries. If you work in project controls, project management, finance, research or are just a general Excel user, power queries are for you.
What is a power query?
A power query is a tool that connects and merges different sources of data in a way that is quick, readable, and repeatable. With power queries, you can extract, transform, load, refresh and more with multiple Excel sheets and tabs. You can easily complete simple but time-consuming tasks like formatting first and last names in large Excel sheets or conduct much more complex tasks that involve cleansing, combining, and sorting data from disparate locations.
Power queries are perfect for monthly reporting- you can set up the query once and then implement/refresh it monthly. Depending on the tasks you hope to complete and how much data you're merging, initial set up for a power query can take anywhere from half an hour to much longer. Power queries are very tailorable to your needs and can be as high-level or in-depth as you need. Once you invest in the upfront build, you’ll save immense time each month. Once built, queries can take only 10 minutes or less to run and create your report.
How I got started with power queries
I initially learned about power queries through my colleagues who are particularly adept at building and using them, including Vayl Sorensen and Manny Abela. In my work, I was spending a lot of time compiling and analyzing data each month and drowning in excel formulas. I actually love Excel – with the right workbooks and formulas, you can do seemingly impossible tasks. But each month, I found myself running out of time to complete the necessary reports. Vayl did a training on Power BI dashboard for our team, and my interest was sparked.
After a bit of hands-on practice with Manny, I jumped into the world of power queries headfirst. I was soon creating reports that could be refreshed quickly and could cascade a single change I made throughout the full document. I can now give clients and partners reports they need in just a few minutes, leaving me more time to focus on big picture questions.
Power query examples
Some of the power queries I’ve built and used have included:
Running a report on cost impacts that merges data from multiple projects into one sheet. Instead of 30 minutes to update this report, I now spend about one minute each time.
A report that pulls info about different stakeholders for the same project in different countries and regions. Now that I have this power query in place, I can tinker with and update the related pivot tables in real time to show hypothetical scenarios during meetings as well.
For one client, I’ve used power queries to help get projects loaded into The Hammer’s CET tool. The client had certain formatting that existed in their COBRA data but not P6, making it impossible to get the data to line up correctly once uploaded to CET. Using a power query, I created a rule to reformat certain information and clean it to get it ready to upload to CET.
How to learn to use power queries
When it comes to power queries, the tried-and-true approach of trial and error is beneficial. Experiment, try things, break things (in sheets you don’t need later, of course!). I’ve also found a lot of help through YouTube how-to videos. Google is also your friend here—if you have a question, someone else has had the same one and is probably talking about it somewhere on the internet. I also highly recommend learning from your knowledgeable colleagues via Zoom screen share or in person when you can.
The future of power queries
As more people learn about and start using power queries regularly I think the bar will continue to rise in terms of what we can do with it. Everyday creativity and need are great drivers of innovation. I am still learning about power queries myself, and my next major goal is to begin creating and using dashboards using power queries.
Even with so much still to learn power queries have greatly benefitted my work. My “Excel nerd” self gets to explore new ways to uplevel and create using data and I have immense pride in the reports I’ve created. It’s fun, too – I’m always wondering what I can make with power queries next. We spend many hours at our desks looking at screens, we may as well take every opportunity to make it interesting and engaging.