Power Query

A tool used to manipulate and transform data for business intelligence projects

Over 1.8 million professionals use CFI to learn accounting, financial analysis, modeling and more. Start with a free account to explore 20+ always-free courses and hundreds of finance templates and cheat sheets. Start Free

What is Power Query?

Power Query is a tool used to manipulate data and is commonly used to transform data for business intelligence projects.

High quality analysis relies on well-organized data that is free from errors and that draws data from a single source of truth. While many analysts spend hours of time manipulating data, combining data and performing lookups, Power Query allows all of these steps to be automated with simple, repeatable steps.

Power Query is one of the most transformative tools available to any analyst working with data in Excel. Not only will it save analysts hours of time, but it will result in reduced manual errors, and a better ability to source data from a single source of truth.

Key Highlights

  • Power Query is a data transformation tool available in both Microsoft Excel and Power BI.
  • Power Query is an invaluable tool for any analyst working in Excel, saving users hours of time, reducing manual errors, and allowing users to source data from a central source of truth.
  • Power Query remembers the user’s data transformation steps, effectively automating lengthy and manual processes.
  • Common use cases are to modify the layout of data, connected to central data sources, merge related tables, or combine multiple files.

Top Uses/Benefits of Power Query

1. Transforming Data

The core purpose of Power Query is to transform data. This means modifying the layout, removing errors, and generally making the data more usable for analysis.

Common data transformations performed in Power Query include:

  • Pivot / Unpivot
  • Splitting / Merging Columns
  • Filtering Data
  • Creating custom columns
  • Deleting header / blank / error rows
  • Changing data types
  • Fill down values

Example: In this example, a user wants to calculate the monthly performance of each store. The problem is that the data in their text file looks like this (below).

This type of data will be very familiar to Excel users, who know just how much work is involved in re-formatting data to fit their analysis needs.

There are unneeded rows at the top of the file, columns of blank values, values to fill down and dates to unpivot.

Power Query - Transforming Data

With a few simple steps Power Query gives the user the ability to transform the data into the below format. And importantly, the process probably took about a minute to complete.

Transforming Data - End Result

2. Repeatable Steps

If the above example isn’t enough, Power Query also remembers each of the steps that were taken to transform the data. This means that when reports are refreshed or changes are made, model inputs can be refreshed in seconds, with no need to re-do the whole process from scratch.

Repeatable steps

Example: In this image, we can see the list of steps that have been applied to the dataset. Each time we update the data source, the steps are applied, and the outputs are updated.

3. Combining Multiple Tables

Power Query allows us to combine multiple tables of related data. Excel users would achieve the same task using lookup formulas. They’d be limited to 1 million rows and find their models became increasingly slow with the addition of so many formulas.

Instead, Power Query joins the tables efficiently using matching columns, does not require any formulas, and is achieved for the entire dataset in seconds.

Example: In this example, we have a table of transactions on the left. In the right table, we have some supporting information about the delivery of each transaction.

Table of Transactions Table of Transactions - Supporting Information

With just a couple of clicks in Power Query, we can use the matching columns to merge the two tables and add the delivery information to the original table.

Table of Transactions - Merged

4. Combining Multiple Files

Power Query includes functionality to seamlessly combine data from multiple similar files.

Example: Suppose we have three similar files, each containing sales data for our stores in a given month. Our model requires a single file with a consolidated list of transactions.

File List

With Power Query, we can easily combine the above files into a single table, as shown below. This can save analysts huge amounts of time and manual error risk.

Multiple Files Combined

5. Connection to Central Data Sources

Power Query includes many connectors which allow you to pull data from SQL databases, web pages, cloud storage, and local files. By always referencing central sources of data, analysts can ensure they are all working from the one version of the truth.

Using the Get Data menu, it’s easy to customize queries to source data from each data source type.

Get Data Menu

For a full list of Power Query connectors available, please refer to the Microsoft Power Query Connectors documentation.

Components of the Power Query Editor

The Power Query editor has the following sections to help you navigate it easily:

  • The Ribbon: Much like Excel, the ribbon helps us get around and complete all our basic tasks at the click of a button.
  • The Data View: This makes up much of the screen and is our primary way to interrogate the data that we’ve imported.
  • Applied Steps & Properties: This is where we can see what transformations have been applied to our dataset and give our query a name.
  • Formula Bar: While Power Query allows us to use a graphical interface to click our way through transformations, it’s secretly converting that into a language called M-code. The formula bar helps us see exactly what is being done and allows us to modify each step if necessary.
  • Query Pane: If we’re working with more than one query or table, the query pane can help us navigate between them.

Who Should Use Power Query?

1. Analysts working in Excel

Anybody working in Excel will drastically improve their ability to work with and manipulate data using Power Query. Instead of manually updating models every week or month, analysts can now automate inputs and focus on their work.

2. Analysts interested in business intelligence

Transforming data for projects is an essential step in the business intelligence process (see below). For that reason, Power Query is a great place to start for any Excel-based analyst interested in Business Intelligence. It makes use of the familiar Excel environment, provides transferable skills into Power BI, and is incredibly easy to use.

The Business Intelligence Process

The Business Intelligence Process

3. Anyone working with data

Even if Excel or Power BI are not your primary BI tools, Power Query is perfect for playing around with data or quickly combining files for other projects. Load the outputs into a worksheet and use it to quickly prototype a BI project with clean data.

How Does Power Query Work?

Power Query effectively works in three stages:

  • First, we connect to the desired data sources, usually with minimal code, and import that data into the Power Query environment.
  • Next, we apply the transformations we want, to filter, combine, sort, group, and merge our data.
  • Finally, we choose where we want that data to go. We can load it into a Power Pivot data model, load it into an Excel sheet, or simply use it as a reference table for another query.

Where Can I Find Power Query?

Power Query is available in both Microsoft Excel and Microsoft Power BI.

Power Query in Excel

Since Excel 2016, Power Query has been available natively. If you have an earlier version of Excel, you may have to download it as an add-in.

In recent versions, Power Query can be accessed via the Data tab in Excel and consists of several buttons in the Get & Transform Data section.

Power Query in Excel

Power Query in Power BI

Power BI is Microsoft’s market-leading dashboarding tool, which allows users to import and transform data, create a data model, and create interactive dashboards.

Power BI makes use of the same Power Query technology to perform all the required data transformations. It can be accessed via the Data and Queries sections of the Home tab.

Power Query in Power BI

To learn more, check out CFI’s Power Query Fundamentals Course!

Additional Resources

0 search results for ‘