When running a project, every project manager (PM) maintains a task list, in some form or fashion, to be able to track their progress. Sometimes the PM manages and maintains one task list; however, when projects scale, more complex tracking techniques are needed. A common problem for PMs is the need to consolidate data from several teams, in the form of task lists, status reports or weekly updates. Normally, a PM might manually combine the data into a spreadsheet but this process can be both tedious and error prone. Thankfully, Excel’s built-in Power Query add-in can easily automate this task.
Power Query is not well known as project management tool, as it is generally thought of as a data manipulation tool. However, Power Query’s features make it ideal for you to develop your own project management tools. Power Query allows you to do three things:
- Connect to various data sources (Excel tables, SharePoint, SQL server, web pages, etc.)
- Clean up, transform and combine the data
- Output to worksheets in your Excel workbook
At WMP, we use Power Query to manage integrations after mergers and acquisitions. Integrations are complex and involve many teams and tasks, and the process produces a lot of information that a project manager needs to organize. Power Query helps here by saving time, eliminating manual work and making analytics easy. Teams enter tasks into individual Excel worksheets. The PM uses Power Query to get the tasks from the teams and merge them in one worksheet. From there, the PM can create charts, analyze data and focus on managing their teams. Without Power Query, the PM would have had to manually copy and paste the tasks or use an unwieldy and slow VBA script.
Power Query is a flexible tool. You can connect to a multitude of data sources, not just other Excel worksheets. You can clean up data by deleting empty rows, replacing values, renaming fields, unpivoting columns and more. For example, if a team made a mistake entering a task, instead of having to navigate to their task plan and fix the mistake, the PM can fix it in Power Query right then and there. If the PM wants to calculate a new column for the number of days left until the due date, they can add a column and use the Power Query language for the calculation.
The advantages to using Power Query are numerous. For one, since it’s based on Excel, it’s easy to implement in any business. Only the PM needs to enable Power Query, so there’s little work required of the teams other than to input data into a spreadsheet. It is fully automated. The PM will always have to most up to date information available. It also allows for extreme customization, which other project management tools may not provide. Lastly, Power Query is similar to the queries in the powerful data visualization tool Power BI, so using Power Query will make using Power BI easier and vice versa.
To get started with Power Query, you need Excel 2010 or later. Power Query is an add-in in Excel 2010 and 2013 and can be downloaded free online. In Excel 2016, Power Query was integrated into Excel and renamed to “Get and Transform.” Both Power Query and Get and Transform have very similar functionality. Find Power Query resources here for Excel 2010 and 2013 and here for Excel 2016. Have fun saving time and seeing more results with Power Query! If you have Power Query questions, please leave a comment below.