Power Query

What is Power Query?

Power Query is a tool used to manipulate data and is commonly used for data analysis and business intelligence. Power Query is available in both Microsoft Excel and Microsoft Power BI.

High-quality analysis relies on well-organized data that is free from errors and that draws data from a single source of truth. Whilst many analysts spend hours of time manipulating data, combining data, and performing look ups, 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.

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.

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

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.

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 top. In the second table below, we have some supporting information about the delivery of each transaction.

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.

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.

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.

5. Connection to central data sources

Power Query includes many connectors that 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.

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

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, by working with 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.

3. Anyone working with data

Even if Excel or Power BI is not your primary BI tool, 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.

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 is 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 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.

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

Additional Resources

• Power Pivot for Excel
• Power BI – Uses in Finance
• Business Intelligence
• Data Migration
• See all business intelligence resources