Excel Power Query Editor: What It Does and How to Use It

How-to basics · Updated November 21, 2026

The Excel Power Query Editor is the workspace where you connect to data, clean and reshape it, and prepare it to load into a worksheet or the Data Model. In Excel for Microsoft 365 on Windows, open it by choosing Data > Get Data and selecting a source, or select a range or table and choose Data > From Table/Range.

What the Power Query Editor Does

Power Query is Excel’s data-import and transformation tool. The Editor lets you create a repeatable sequence of changes—such as removing unwanted columns, filtering rows, changing data types, or combining files—without altering the original source. When you refresh the query, Excel retrieves the data again and applies those steps in order.

This is useful when you receive recurring reports in inconsistent or overly detailed formats. For example, a monthly sales file might contain extra title rows, blank lines, columns you do not need, and dates stored as text. You can clean those details in the Editor once, then refresh the query with the next month’s file instead of repeating each manual edit.

Power Query is commonly described as an extract, transform, and load tool: it extracts data from a source, transforms it into a useful shape, and loads the result to an Excel destination. It does not replace the source file. The query stores instructions for retrieving and preparing the data, while the loaded output is a result you can analyze with formulas, charts, or PivotTables.

How to Open It in Excel

The route depends on where the data is coming from. In Excel for Microsoft 365 on Windows, use one of these options:

  • Import a file: Choose Data > Get Data, select a source such as From File > From Workbook or From Text/CSV, choose the file, and select Transform Data when the preview appears.
  • Start from an Excel range: Select a cell in the data, then choose Data > From Table/Range. If prompted, confirm whether the first row contains headers.
  • Open a saved query: Choose Data > Queries & Connections. In the pane, right-click a query and choose Edit, or double-click the query.

Some source selections show a preview or Navigator before the Editor opens. Select Transform Data to make changes in the Editor; choosing Load immediately may send the data to Excel without opening that workspace. To create a query without first selecting a source, use Data > Get Data > From Other Sources > Blank Query when that command is available in your Excel build.

Excel for Mac has Power Query features in Microsoft 365, but available connectors and capabilities can differ from Windows and can depend on the Excel version and update channel. Excel for the web also has a different feature set. If a command or connector described for Windows is missing, check Excel’s update status and Microsoft’s current feature documentation for your platform.

Understanding the Editor Window

The Editor is arranged to make the incoming data and the transformation history visible. The central preview shows rows and columns, while the ribbon groups commands for working with the query. The exact controls shown can vary with the selected column, data source, and Excel version.

  • Preview grid: Displays a sample or preview of the data and lets you select columns, filter values, and inspect results. It is not necessarily a complete view of every row in a large source.
  • Ribbon: Home contains common actions for managing queries, choosing data, and loading results. Transform provides operations on existing columns and tables; Add Column creates new columns. View controls options such as the formula bar and query settings pane.
  • Applied Steps: The Query Settings pane records transformations in sequence. Selecting a step previews the result at that point; deleting or changing a step can affect later steps.
  • Formula bar: When enabled, it displays the Power Query expression for the selected step. The underlying query language is called M.

The step list is important because transformations are not just a set of independent clicks. A later step may rely on a column or value created earlier. If you change an early step, inspect subsequent steps for errors or changed results.

A Typical Data-Cleaning Workflow

Imagine a workbook containing a table of orders with columns for order date, customer, product, quantity, and a notes field that is not needed for reporting. A practical workflow in the Editor might look like this:

  1. Check that the correct worksheet or table was selected in the source preview, then promote the first row to headers if the file has generic column names.
  2. Remove the notes column and filter out blank rows or records that do not belong in the report.
  3. Set the order date column to a date type and quantity to a whole-number type. Check that values convert as expected, particularly if dates use a different regional format.
  4. Rename unclear headings, reorder columns, and remove duplicates only if the business meaning of a duplicate is understood.
  5. Review the preview and the Applied Steps list, then choose Close & Load to return the prepared result to Excel.

These changes affect the query output, not the original order file. That distinction makes Power Query safer than editing a source report by hand, but it also means the original file remains unchanged if you need to audit or correct it.

Transforming Data and Setting Types

Common transformations include filtering rows, sorting, splitting a column by a delimiter, replacing values, removing errors, grouping records, and pivoting or unpivoting columns. For example, a column containing entries such as “North – Retail” could be split at the hyphen into region and channel fields. A report with one column for each month could be unpivoted into a consistent Month and Amount structure, which is often easier to chart or summarize.

Data types deserve particular attention. Power Query can identify text, numbers, dates, times, and other types, but automatic detection can make a wrong guess. A product code such as 00127 is an identifier, not necessarily a number; converting it to a numeric type may remove its leading zeros. Likewise, a date interpreted with the wrong locale can be misread or produce errors. Set types deliberately and check representative values before loading.

Filtering in the Editor is a transformation step, not merely a temporary view setting. If you filter out rows, they will remain excluded after refresh unless you change or remove that step. This is useful for repeatable reporting, but it can lead to incomplete results if the filter was added without considering future data.

Combining Files and Tables

Power Query can combine data from multiple sources, which is useful for recurring exports. If monthly files share the same column structure, you can connect to a folder and combine the files into one query. When a new matching file is added, refreshing can bring its rows into the result. Keep the folder limited to the intended inputs: unrelated files, temporary copies, or files with different layouts may cause unexpected output or errors.

Queries can also be combined using two different ideas. Append stacks rows from one table beneath another, much like adding more records to a list. Merge matches tables using one or more key columns and adds related fields, similar in purpose to a database join or a lookup. For instance, append separate regional sales tables to build one transaction list; merge that list with a product table to add product category. Before merging, check that the key values use compatible types and that the lookup table does not contain unintended duplicate keys.

Loading Results and Refreshing Queries

When transformations are ready, choose Home > Close & Load to load the result using the default destination. Use the Close & Load dropdown and choose Close & Load To when you need to select a destination, such as a table in a worksheet or the Data Model. A connection-only query can be useful as an intermediate step when another query will use its result; it avoids placing every preparation query on a worksheet.

After loading, use Data > Refresh All to update workbook queries, or refresh an individual query from the Queries & Connections pane. Refresh reruns the source connection and the saved transformation steps; it does not mean Excel will automatically notice every change at every moment. A query can fail if a file was moved, a column was renamed, a server is unavailable, or credentials or permissions have changed. Some refresh behavior and source support depend on the connector and Excel platform.

For repeat reporting, keep the source location and column structure stable where possible. If the report’s layout changes, inspect the query preview and applied steps instead of assuming that a successful refresh produced the intended analysis.

Power Query, Formulas, and Other Excel Tools

Power Query and worksheet formulas solve related but different problems. Formulas calculate within the worksheet and can respond to edits in cells. Power Query prepares and reshapes imported data through a sequence that is normally rerun when you refresh. Use formulas for calculations that should update as users change worksheet inputs; use Power Query for repeatable import and cleanup work.

A PivotTable summarizes data after it has been prepared, while Power Query focuses on obtaining and shaping that data. They work well together: clean several source files with Power Query, load the consolidated table, then build a PivotTable for totals by month or region. You can also load query results to the Data Model when you need to work with related tables and larger analytical models.

Power Query does not make every data task easier. For a small, stable list that needs one quick correction, a direct worksheet edit may be simpler. For complex transformations, queries provide a visible and repeatable process, but users still need to understand the steps, source assumptions, and refresh conditions.

Common Problems and Practical Checks

If a query returns an error, identify the first failing step in Applied Steps. A step may refer to a column that was renamed or removed, expect a date where the source now contains text, or depend on a file path that has changed. Check the preview at the failing step and compare it with the source rather than deleting steps at random.

  • Unexpected blanks or missing records: Review filters, removed rows, and the source selection. A filter that worked for last month may exclude new categories.
  • Numbers or dates look wrong: Check the assigned data type and locale, and verify leading zeros in identifier fields.
  • A folder combine fails: Confirm that the files have compatible headers and formats, and remove unrelated files from the folder.
  • Refresh asks for access or fails: Check the source path, credentials, network access, and any organizational permissions. Do not lower privacy or security protections simply to bypass a warning.

For external sources, use only files and services you are authorized to access. Treat downloaded workbooks and data sources cautiously, especially if their origin is unknown. Power Query transforms data; it does not certify that a source is accurate, safe, or appropriate to use.

Frequently Asked Questions

Is Power Query Editor included in Excel?

Power Query is integrated into supported desktop versions of Excel, including Microsoft 365 for Windows. The available connectors and editing features vary by platform and version, so Mac and web users may not see every Windows command.

How do I reopen Power Query Editor?

In Excel for Microsoft 365 on Windows, choose Data > Queries & Connections, then right-click the query and choose Edit, or double-click it. The Editor opens with the query’s saved steps.

Does Power Query change my original data?

No. Its transformations are applied to the query result, not written back to the source file. If you load the result into the same workbook, that output is separate from the original source unless you explicitly edit the source yourself.

Why is Transform Data missing?

The wording and location depend on how you started the import and on your Excel version. Some routes show a source preview with a Transform Data option; other routes may open the Editor directly or load the data. Check that you selected a supported source and that Excel is up to date.

Will a query update automatically when the source changes?

Usually you need to refresh the query, for example with Data > Refresh All. Refresh retrieves the current source data and reapplies the saved steps, provided the source is reachable and its structure still matches what the query expects.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.