A Comprehensive Explanation of Excel Power Query

For many data, finance, and accounting professionals Power Query is the answer to many of their data challenges but few are familiar with its capabilities. We’ll explain what Power Query does, who its designed for, and highlight some of the most common use cases.

Power Query is a feature set in Microsoft Excel that allows people to work with large data sets and record applied steps to automate data prep and processes.

Power Query is a feature set of Microsoft Excel, Power BI, and Analysis Services designed to help people automate routine data transformation and preparation tasks. It’s capable of efficiently handling large amounts of data and can save Excel users countless hours of time typically spent building spreadsheets.

Let’s take a look at what it is, and how to get started!

What is Power Query in Excel?

Power Query, a data connection technology, enables Excel users to discover, connect, combine, and refine data across a wide variety of sources. Initially released as an add-in for Excel 2010 and 2013, it’s now an integral part of Excel 2016 and later versions. Power Query’s primary function is to facilitate advanced data extraction, transformation, and loading (ETL) tasks in a user-friendly way.

When you launch the Power Query Editor in Excel, it will look like the screenshot below.

At a high level it’s made up of 3 sections. The top ribbon that contains many different tools, actions, and steps that you can apply to your data, such as multiplying column, de-limiting columns, concatenating columns etc.

Then on the right side is a section of Applied Steps. This keeps track of all of the actions applied to the data in the middle of the page.

Data is prepared at the column level, meaning that any action applied to a column will automatically be applied to all of the rows in that column.

Because actions are applied column by column, the data must be in one continuous table for actions to be appropriately applied to them. Working in Power Query is similar to working with data like you would in a database, or business intelligence software like Power BI or Tableau.

Once the data is setup the way that you need it to be, you can close out of Power Query and it will automatically apply the steps to your data.

When you update the source data, such as pointing Power Query to a new spreadsheet to start with it runs through the applied steps and automatically preps your spreadsheet. In some ways it is similar to recording steps in a macro but no coding is required and you can visually see the changes to your data each step of the way.

Some of the key benefits of using Power Query Include:

  • Integration with Multiple Data Sources – Connects to various databases, online services, folders of spreadsheets.
  • Data Transformation Tools – Wide range of tools for sorting, filtering, merging, cleaning and prepping data.
  • Highly Repeatable: Allows users to modify data once, and re-use many times.

If you’re on the fence about investing time into learning Power Query, it’s an incredibly useful tool that’s used across a number of Microsoft technologies and can help you future proof your career.

Let’s look at the history of Power Query and how it’s still relevant today.

A Brief History and the Future of Power Query

Power Query was introduced by Microsoft as an add-in for Excel 2010. It then became a built-in feature for all versions since Excel 2016. It regularly receives updates and is the same interface that Microsoft uses in many of its different business intelligence and data transformation products.

Power Query is the same set of features used to prepare data in Power BI, Microsoft’s Business intelligence tool and has recently been updated to run on the cloud through PowerBI.com, as shown in the screenshot below.

Microsoft has also launched a product called Microsoft Data Wrangler that uses an interface similar to Power Query that generates Python code designed to help data scientists prep data for analysis without having to manually write code.

Excel is one of the most widely used pieces of software on the planet, but as companies evolve and ask employees to do more with less time, automation and skills using more advanced software are becoming a necessity.

By learning Power Query in Excel, you can give yourself a head start on learning a set of features that are widely used in other popular software tools along with gaining new capabilities to automate and streamline your current work.

With Microsoft putting so much effort into expanding the use of Power Query it’s unlikely to become a tool that you learn and won’t be able to utilize in the future.

Power Query Use Cases

If you’re wondering when you should utilize Power Query there are a number of different scenarios that you can use to take advantage of it that range from automating repetitive tasks, working with large datasets, and connecting to different data sources.

  • Data Cleaning – Streamline cleaning data by removing duplicates, handling missing values, transforming formats.
  • Data Integration – Combines data from different sources for comprehensive analysis.
  • Automating Data Processes – Automates repetitive tasks, saving time and reducing errors.

To make it a little more real, here are a couple of potential use case scenarios.

Example Scenario #1 – Prepping a Monthly Accounting Report

When putting together reports on a monthly basis, it typically involves the same series of steps. Download a report from accounting software, open it in Excel, remove the top header rows, delete unnecessary columns, and summarize the data in a Pivot Table or use SUMIF and VLOOKUP formulas to bring it into a formatted report tab.

Power Query can be used to record the first series of steps, so that when the next month comes you update the input file to point to the new accounting data and press refresh.

This saves you time by not having to run through the steps of getting the data into a format where you can report on it.

Example Scenario #2 – Automating Data Imports and Applied Steps

Taking the example from above a step further, rather than importing an accounting report into Excel, many times you can connect Power Query directly to the database that runs accounting software. You would import data into Power Query, use different steps to filter data to the relevant date range, remove unwanted columns, and publish the report to Excel Online to schedule automatic refreshes.

Because the report is based on a live data connection, there’s no need to run a report and once the data transformation steps are established and scheduled the report updates itself saving you even more time.

Launching Power Query in Excel

To access Power Query in Excel 2016 or later, navigate to the Data tab on the Microsoft Excel Ribbon. Either select Get Data to import a file or connect to a data source, or select rows and columns that are in a tabular format, meaning no breaks in column headers and press the From Table / Range button to launch Power Query.

The example below shows data that’s been selected in Excel, and the location of the From Table/Range button. The Get Data button is to the far left.

Screenshot of how to launch the Power Query editor in Excel starting with a table of data

For Excel 2010 and 2013 you’ll need to download and install the Power Query add-in. Once installed, a new ‘Power Query’ tab will appear.

Power Query launches in a new window.

Make any changes necessary to your data in Power Query and when you are finished press Close & Load on the left side of the Home Ribbon of the Power Query Editor and the changes will be saved.

Screenshot of the Power Query Editor in Excel being opened in a new Excel window and using the Close and Load feature to save applied steps

To refresh Power Query data, click on the “Refresh All” button on the Data tab in Excel. As long as the format of your starting data is the same as a previous version that the applied steps were created with Excel will automatically run through each step and update the data.

Conclusion

Power Query is a transformative tool in Excel that empowers users to handle data more efficiently and effectively. Whether you are a seasoned data analyst or just getting started, understanding and utilizing Power Query can significantly enhance your workflow and allow you to partially or fully automate many different repetitive processes that are common in the data and accounting worlds.

Scroll to Top