What is Power Query in Excel?

What is Power Query in Excel and Why is It So Useful?

by Avantix Learning Team | Updated November 18, 2023

Applies to: Microsoft^® Excel^® 2016, 2019, 2021 and 365 (Windows)

Power Query in Excel is a powerful data transformation tool that allows you to import data from many different sources and then extract, clean, and transform the data. You will then be able to load the data into Excel or Power BI and perform further data analysis. With Power Query (also known as Get & Transform), you can set up a query once and then refresh it when new data is added. Power Query can import and clean millions of rows of data.

One of the great things about Power Query is that you don't need to learn or use any VBA (Visual Basic for Applications) code to query and refresh data. The Power Query Editor records query steps so the query can be reused when needed. The steps are converted into M code (specific to Power Query) for you.

Although you can learn to write and edit your own M code, you don't need to write code to use Power Query.

Recommended article: How to Merge Cells in Excel (4 Ways)

Do you want to learn more about Excel? Check out our online (virtual classroom) or in-person Excel courses >

In this article, we'll review:

What can Power Query do?
Where is Power Query?
Import data using Power Query
Common query types

Note: Screenshots in this article are from Excel 365 but are similar in other versions of Excel (2016 and later).

Power Query in Excel is similar to Power Query in Power BI.

What can Power Query do?

With Power Query, you can:

Split columns based on delimiters such as commas, spaces or dashes.
Apply transformations to text such as removing leading and trailing spaces and changing case.
Change data types.
Convert invalid dates to valid dates.
Remove columns, rows or blanks without affecting the original data set.
Sort and filter columns.
Merge columns.
Create calculated columns including conditional columns and columns by example (using Power Query's M language).
Summarize or aggregate data.
Unpivot (flip) data (to use with pivot tables).
Save steps so you can reuse a query as an alternative to using VBA (Visual Basic for Applications) code to automate steps.

Where is Power Query?

In 2016 and later versions, Power Query has been fully integrated into Excel. You access the Power Query Editor through the Data tab in the Ribbon in the Get & Transform group. Power Query has its own Ribbon with different tabs and commands compared to the Excel Ribbon.

Power Query options to import data appear on the Data tab in the Ribbon as follows:

Import data using Power Query

It's easy to import data using Power Query. Excel provides many types of data connections that are accessible through the Get Data drop-down menu in the Data tab in the Ribbon.

With Power Query, you can:

Get data from a table in the current workbook.
Get data from a single file such as an Excel workbook, text or CSV file, XML and JSON files.
Get data from multiple files in a folder.
Get data from various databases such as SQL Server, Microsoft Access, Analysis Services, SQL Server Analysis Server, Oracle, IBM DB2, MySQL, PostgreSQL, Sybase, Teradata and SAP HANA databases.
Get data from Microsoft Azure.
Get data from online services like Sharepoint, Microsoft Exchange, Dynamics 365, Facebook and Salesforce.
Get data from other sources from the web, a Microsoft Query, Hadoop, OData feed, ODBC and OLEDB.
Merge two queries together.
Append a query to another query.

The available options will depend on your version of Excel.

The most common query types are available in the top level of the Data tab in the Ribbon in the Get & Transform group.

Common query types

Common query types include:

From a table or range
From a text or CSV file
From an Excel workbook
From a folder

You can use the Get Data drop-down menu to get data from different sources:

If you connect to an external source, depending on which type of data connection you choose, a wizard will appear to prompt you through the connection set up and there may be several options to select during the process.

At the end of an import or connection and setup process, a Data Preview window appears where you can view the data to make sure it's set up the way you want. You can then load the data to Excel or edit in the Power Query Editor to apply any data transformation steps you want. There are other options as well (such as combining and editing).

If you create a query from a table or range in an Excel workbook, Power Query will launch the Editor automatically without the wizard.

Subscribe to get more articles like this one

Did you find this article helpful? If you would like to receive new articles, JOIN our email list.

More Resources

How to Show or Hide Gridlines in Excel

How to Merge Cells in Excel (4 Ways with Shortcuts)

10 Great Excel Keyboard Shortcuts for Filtering Data

How to Insert Multiple Rows in Excel (4 Fast Ways with Shortcuts)

How to Highlight Errors, Blanks and Duplicates in Excel Worksheets (Using Formulas)

Related courses

Microsoft Excel: Introduction

Microsoft Excel: Intermediate / Advanced

Microsoft Excel: Data Analysis with Functions, Dashboards and What-If Analysis Tools

Microsoft Excel: Introduction to Power Query (Get & Transform)

Microsoft Excel: Introduction to Power Pivot

Microsoft Excel: New Features and Functions in Excel 365

Microsoft Excel: Introduction to Visual Basic for Applications (VBA)

VIEW MORE COURSES >

Our instructor-led courses are delivered in virtual classroom format or at our downtown Toronto location at 18 King Street East, Suite 1400, Toronto, Ontario, Canada (some in-person classroom courses may also be delivered at an alternate downtown Toronto location). Contact us at info@avantixlearning.ca if you'd like to arrange custom instructor-led virtual classroom or onsite training on a date that's convenient for you.

How to Replace Zeros (0) with Blanks in Excel

There are several strategies to replace zero values (0) with blanks in Excel. If you want to replace zero values in cells with blanks, you can use the Replace command or write a formula to return blanks. However, if you simply want to display blanks instead of zeros, you have two formatting options – create a custom number format or a conditional format.

How to Freeze Rows in Excel (One or Multiple Rows)

You can freeze one or more rows in an Excel worksheet using the Freeze Panes command. If you freeze rows containing headings, the headings will appear when you scroll down. You can freeze columns as well so when you scroll to the right columns will be frozen.

How to Show or Hide Gridlines in Excel

You can remove or hide gridlines in Excel worksheets to simplify worksheet design. By default, gridlines are displayed but do not print. Gridlines are applied to entire worksheets or workbooks, not to specific cells. If you hide gridlines on one worksheet, it doesn't affect other sheets in the same workbook.

What is Power Query in Excel?