The Ultimate Reporting Tool that You Already Have: Excel Power Query

From annual emissions reporting to periodic environmental reports to emissions projections and baseline/actual emissions comparisons needed for permitting, calculation and reporting tasks can be tedious and repetitive. Although there are many tools to streamline the calculation process, the most affordable and accessible is Excel Power Query, available with any Microsoft Excel license, and which creates dynamic tables with live data and the option of a seamless integration with Power BI.

Power Query is a tool native to Microsoft Excel that functions in the background of any .xlsx file. It is Excel’s way of making sense of any imported data, and once it is set up, a Power Query can be used over and over again to run the same calculations with new data. You can use a Power Query to streamline data processing for many kinds of environmental reports and calculations.

Set up a Power Query once, have near-instant emissions calculations for years to come.

How does this tool work?

Power Query is simple to learn, especially if you are already accustomed to doing moderate-to-complicated tasks in Microsoft Excel. The Power Query editor is set up similar to a spreadsheet but with a list of every change that has been applied to the data. That same list of changes can then be applied to fresh data by either refreshing the data pull or changing the data source. Using Power Query, you can run calculations, summarize data, filter and clean data, and create custom formulas to apply to data every month, quarter, year, etc.

Excel Power Query is highly effective at importing mass amounts of data into Excel and formatting it consistently. You can import entire folders of data at a time, which comes in handy when consolidating daily or monthly equipment data across many files. The Power Query then informs Excel how to interact with this data. Within the Power Query you can clean your tables, filter the data, and add calculations and transform your data. Power Query also connects seamlessly with other Microsoft tools like Excel Power Pivot and Power BI, which makes it that much easier to summarize data and create live reports.

What kind of data file types can Excel Power Query use?

A wide variety of file types are acceptable, including .xlsx, .xlsm, .csv, .txt, .pbix, common database files, and most notably .pdf. If your PDF files are readable in Adobe Acrobat or any similar digital PDF editor, then they can likely import successfully into Excel Power Query.

How do I know if this would work for my reporting tasks?

Consistent data formatting is key to successfully implementing a Power Query. For instance, if your data is coming directly from a CMS or an internal server such as PI SMT, you can be confident that the data formatting will be consistently similar from one year to the next and thus that an Excel Power Query will work well with this data.

How many Power Queries will I need to set up?

You only need one Power Query for each calculation type. For example, if you have three power boilers and two lime kilns, you only need two Power Queries because the first Power Query can be used for all power boilers and the second Power Query for both lime kilns, as long as the throughputs are measured the same way across each equipment type (e.g., heat input of boiler fuel and kiln throughput).

How long does it take to set up a Power Query? 

Depending on your reporting needs, a simple Power Query can be set up in just a few hours. That can add up to dozens or hundreds of saved hours over the lifetime of the Power Query.

Can I use a Power Query that someone else set up? 

Yes, even without a tutorial from the person who created it. Using Power Query’s list of all applied steps, you can easily familiarize yourself with a given Power Query by clicking through the steps to watch the data processing unfold.

Does this fully automate the calculations I need for all my reporting?

Unfortunately, no – Excel Power Query is best at automating individual calculation steps and is no replacement for a more comprehensive digital solution. If you are interested in exploring a fully-automated or partially-automated reporting system, our Digital Solutions Practice can connect you with the best product for your needs. Reach out to Stephanie Taylor (staylor@all4inc.com) for more information.

How do I get started?

You can learn how to set up a Power Query yourself using any of the countless online resources. Alternatively, we at ALL4 would be happy to help you implement a Power Query to use for years to come. If you would like to start the process of automating the calculation steps of your environmental reports, I encourage you to reach out to your ALL4 project manager or you can connect with me at opearson@all4inc.com.