Power Query in Excel: Definition, Functions and Phases

By Indeed Editorial Team

Published 16 May 2022

The Indeed Editorial Team comprises a diverse and talented team of writers, researchers and subject matter experts equipped with Indeed's data and insights to deliver useful tips to help guide your career journey.

Excel is a versatile tool that helps many professionals, from accountants, data scientists to economists, perform simple to complex tasks every day. One reason for Excel's immense flexibility is the ability to import, sort and analyse data from different sources using a power query. Knowing how to use a power query efficiently can enhance your Excel skills, make your work faster and improve your results. In this article, we define what a power query is, explain its different phases and outline the functions it can perform.

What is a power query in Excel?

A power query in Excel is a tool that allows you to import data from different file sources into Excel for easy sorting, analysis and presentation. In Excel, a query is a set of rules that allow you to automate data processing. Once you save a query, it continues to perform its function every time you refresh the data. For example, if you upload sales data every month to calculate commissions to pay employees, all that's required is to set up a query once and refresh it monthly to calculate payments for your sales team.

You can use a power query to import and filter millions of rows of raw data into a wide range of statistical models for easy analysis and manipulation. Thanks to a text-based editor that has an intuitive user interface, you can instruct the program to display a specific data subset within a larger set. The tool can also merge tables and process a wide range of data formats. Unlike other powerful Excel tools, power query doesn't require coding.

Related: Excel Skills: Definition and Examples

Functions of power query

Power query is an integral part of Excel, so it can access all the functions of the program. Here are some of the common tasks people use power query for:

Perform "VLOOKUP" operations

"VLOOKUP" allows users to find specific data points in a range of cells, such as cells B1 to Y10. When referencing cells this way, you use the notation "cell name:cell name" to instruct the program of the exact cells to include in the function. "VLOOKUP" is ideal for cleaning small data sets.

Combine tables

You can also use a power query to combine tables. If you make regular additions to the same data source, such as weekly or daily, adding new data to the main file manually can be time-consuming and error-prone. Thankfully, power query simplifies the process. You can use the tool to combine different data sets from various periods. All that's required is to create the query and refresh it whenever additional data sets become available.

Merge tables

If you're working with a large dataset, such as one with thousands of rows, you can use the merge option in power query to join these tables for easier sorting and analysis. For example, if you want to combine the sales data for different products sold by several employees for five years. While "VLOOKUP" can perform this function, it can only handle small data sets. The merge operation can help you join sales data for each employee for several years, speeding up your work and results.

Automate data imports from various sources

Power query allows you to program your Excel to import data from various source files, such as Excel workbooks, CSV, text and a wide range of other formats and sources. Once you create a query for importing a specific data source, power query stores these instructions. Whenever you want to import the same type of data, all that's required is a refresh of the query to perform the function.

Related: How to List Microsoft Office Skills on a Resume (With Examples)

Phases of a power query in Excel

Power query executes four phases, starting from the user input to the result. Here are the descriptions of these phases and the user's inputs:

1. Connect

In the first phase, you connect to the data that you want to manage. This is the same thing as importing data from a source file on your computer, a service, the internet or the cloud. After connecting the different data sets, you can then perform various operations, such as combinations and transformations. Save the worksheet to retain the changes you made. In the future, refresh the data to add, delete or change the external data files. Follow these steps to import data sets into power query:

  1. Select the "Data" tab from the ribbon at the top of the Excel application.

  2. Navigate to the "Get & Transform Data" tab and choose "Get Data" from the drop-down menu.

  3. Place your cursor on the "From File" option to open another menu.

  4. Select the location of the file you want to import data from. You can choose "From Text/CSV," "From Workbook" and other applicable locations.

  5. Click "Browse" to search for the file you want to import.

  6. When you locate the file, click "OK."

Once you select "OK," the program displays a dialogue box where you can choose from three operations to perform on the data. The options include:

  1. "**Combine & Transform Data"**: Choosing this option allows you to clean and organise the data, such as sorting it into tables and columns.

  2. "Combine & Load": This option imports data directly from the first file to Excel. If the data you want to import is sorted already, you can use this option to skip the "Combine & Transform Data" phase.

  3. "Combine & Load To...": This option allows you to export the data from a source file and import it into another location via the Excel workbook.

Related: 16 Must-Have Data Analyst Skills (Plus Job Responsibilities)

2. Transform

This phase allows you to change how Excel displays data without altering the original. You can use it to combine, separate or merge columns, organise data numerically or alphabetically and change the data in several other ways. Power query displays the results of data transformation operations through a dedicated editor. To open the editor, go to the "Get & Transform Data" group and select "Launch Query Editor" from the "Get Data Command." Excel also opens the editor when you connect to a data source, create a new query or load an existing query.

Note that the editor records every action you perform on the data and labels each step and transformation you apply. If you want to view any changes you made to the data, navigate to the "Query Settings" pane and click "Applied Steps." A query is the combination of all the transformations you applied to data connections. To apply the same changes to other data sets, just refresh the query and Excel executes the modifications automatically. Here's how to use the transform phase:

  1. Select "Transform Sample File".

  2. Make your changes to the data.

  3. Click "Apply" so the query can make the changes.

3. Combine

Once you change the data in one file, you can add other data files to the Excel workbook. This phase allows you to integrate data from multiple sources to get a fuller perspective or perform complex analyses. You can use this phase to combine multiple queries in an Excel workbook using the "Append" and "Merge" operations. These operations apply to tabular-shaped queries that are independent of their data source files. Here's what you can do with these operations:

  • "Append": An "Append" operation creates a new query that comprises all the rows from a first query followed by the entire rows from a second query. These operations can be intermediate or inline.

  • "Merge": This operation creates a new query from two existing queries. The operation allows you to add or expand columns from a related table into a primary table, which is the first data set you combined with another.

The query makes the same changes it made to the first data set automatically to every new file you include in the workbook. That way, you only require modifications to one data set and Excel is going to apply those changes to additional files you include in a workbook.

Related: 20 Accounting Skills for Your CV (Plus How to Improve Them)

4. Load

Once you've performed operations on your imported data, save the query and the workbook for future use. During this phase, you can choose where you want the data to load from when you import it. Follow these steps to choose the load location for a data set in the power query editor:

  1. Select "Close & Load To..." from the top left of the Excel ribbon.

  2. Choose a location for the new data to load.

  3. Click "OK" to allow Excel to import the data to the chosen location.

Another way to choose load options is to use the "Query Options" dialogue box. To do this, select "File," then "Options and settings," then click "Query Options." Here, you can choose the load location for your data and how you want Excel to display it. Options include loading from a worksheet or a data model, which is a collection of multiple tables in a workbook.

Please note that none of the companies, institutions or organisations mentioned in this article are affiliated with Indeed.

Explore more articles