Power Query Editor: What It Is, How It Works, and Why

Power Query Editor is the data-preparation window in Microsoft Excel where you connect to data, clean and reshape it, and save the steps as a query that can be refreshed later. Instead of repeating the same cleanup by hand, you define a sequence of transformations once and apply it again when the source data changes.

What Power Query Editor does

Power Query Editor is part of Excel’s Get & Transform Data tools. It provides a visual workspace for importing information from sources such as workbooks, text files, folders, databases, and online services. Inside the editor, you can filter rows, change data types, split columns, combine tables, remove duplicates, and carry out many other preparation tasks before the data is used in a worksheet or data model.

The word “query” can be confusing here. A Power Query is not simply a question asked of a database: it is a saved set of instructions for obtaining and shaping data. The editor helps you create and adjust those instructions. When you are done, Excel can load the result to a worksheet table, create a connection without placing the result in a sheet, or add the result to the Data Model for analysis.

The editor is therefore a preparation tool, not a charting or dashboard designer. It can make information more consistent and analysis-ready, but you still use Excel features such as PivotTables, formulas, charts, or the Data Model to explore and present the prepared results.

Background: Power Query in Excel

Power Query first appeared as an add-in for some earlier editions of Excel and became part of the built-in Get & Transform experience in Excel 2016. In current Microsoft 365 Excel for Windows, it remains a central way to import and transform data. The same underlying technology is also used in Power BI, although the surrounding applications and some available features differ.

This history explains why people may use several names for related parts of the feature. “Get & Transform Data” is the Excel ribbon group, “Power Query” is the technology and query experience, and “Power Query Editor” is the window where transformation steps are configured. In everyday discussion, these terms are sometimes used interchangeably, but they do not refer to exactly the same screen or action.

Queries can be useful even when the source is a simple CSV file. Their main advantage is repeatability: the transformation instructions remain saved in the workbook. That makes them especially helpful for recurring reports, monthly exports, and files that need the same cleanup each time.

How to open the editor in Excel

In Excel for Microsoft 365 on Windows, the route depends on whether you are creating a query or reopening one. To start from a range, select a cell in the data and choose Data > From Table/Range. Excel may ask you to confirm or create a table; the Power Query Editor then opens with that table as its source. To connect to another source, use Data > Get Data and select an available source or connector.

To edit an existing query, choose Data > Queries & Connections. In the pane, double-click the query, or right-click it and choose Edit. The editor opens with its previous steps intact. After editing, use Home > Close & Load, or the Close & Load To option when you want to choose where the result goes.

Excel for Mac also includes Power Query capabilities in Microsoft 365, but connector availability and some authoring options differ from Windows and can vary by Excel build. If a particular source or command is missing, check the current Microsoft support information for your version rather than assuming every Windows feature is present. Power BI Desktop is a separate Windows application, not a Mac version of the Excel editor.

How queries and transformation steps work

When a query opens, the editor typically shows a data preview in the center, a ribbon of transformation commands, and a Query Settings pane. The pane’s Applied Steps list records the operations already performed. Selecting a step lets you inspect the result at that point; deleting or changing an earlier step can affect every later step.

Each command contributes an instruction to the query. For example, a sales-import query might connect to a CSV file, promote the first row to headers, set the date and amount columns to appropriate types, remove blank rows, filter out cancelled orders, and group sales by month. The order matters: filtering before or after a merge can change the records that remain.

A practical workflow is to keep the source unchanged and build transformations in a query. That gives you a repeatable process and makes it easier to diagnose where a result changed. Before loading, inspect the preview and check that columns have sensible names, types, and values. The preview is useful for checking the transformation, but it should not be mistaken for a guarantee that every record in a very large source has been visually examined.

Useful transformations and a concrete example

Imagine receiving a new workbook each month with order records. One file contains dates stored as text, customer names with extra spaces, and a status column that includes cancelled orders. You could import the file, convert the date column to a date type, trim the customer-name column, filter the status to keep only completed orders, and remove columns that are not needed for the report.

Common transformations include:

Filter and sort: Keep rows that meet conditions, or arrange records by date, name, or value.
Change data types: Interpret values as dates, whole numbers, decimals, text, or other supported types.
Clean and reshape: Remove empty rows, rename columns, split a combined field, or pivot and unpivot columns.
Combine information: Append tables with similar columns, or merge tables using matching fields such as a customer ID.
Summarize: Group rows and calculate aggregates such as a count or a total.

For the next month, replace or update the source file and refresh the query. Excel reruns the saved sequence against the new input. If the new file follows the same structure, the report can be updated without repeating each manual cleanup step. If its layout changes—for instance, a required column is renamed—the query may need to be adjusted.

Applied Steps, M code, and query folding

Power Query stores its transformation logic in a language called M. Most users do not need to write M: choosing commands in the ribbon or context menus creates the corresponding instructions automatically. The Formula Bar and Advanced Editor can expose the code for people who need to inspect or customize a query. M is different from worksheet formulas and VBA; it describes the process for retrieving and transforming data.

The Applied Steps list is a readable outline of that process, not a separate set of results to copy. Renaming steps with meaningful labels can make a complex query easier to maintain. For example, “Filtered Cancelled Orders” explains more than a generic step name. When you change a step, check downstream steps because a later operation may rely on a particular column name or data type.

For some sources, Power Query can translate supported transformations into operations run by the source system. This is called query folding. When folding is available, a database may do filtering or aggregation before sending records to Excel, which can reduce the amount of data transferred. Not every connector or transformation can fold, and adding an operation that cannot be translated may stop folding for later steps. The editor’s behavior depends on the source and the particular operations, so it is not safe to assume that every query is processed entirely at the source.

Loading, refreshing, and where results go

Closing the editor does not always mean that the result appears in a worksheet. Close & Load uses the query’s current load settings; Close & Load To lets you choose an output such as a table in a new or existing worksheet, a connection only, or—in supported Excel workflows—the Data Model. A connection-only query can still be used as an input to another query, which is useful when a workbook has several preparation stages.

After loading, refresh the result using Data > Refresh All or the refresh command for an individual query. Refresh reruns the query against its source; it does not necessarily refresh every external system instantly or remove the need for access permissions. A file-based query may depend on the source remaining at the expected path, while a database or online source may require valid credentials and network access.

Refresh settings can be managed through the query’s properties in Excel, but available settings depend on the source and workbook. If a report is shared, make sure recipients understand where the source data lives and whether they have permission to refresh it. A workbook can display previously loaded results even when another user cannot access the original source to obtain updated data.

Power Query compared with formulas and manual cleanup

Power Query and worksheet formulas solve different, sometimes overlapping problems. Formulas calculate results in cells and can respond directly as referenced cell values change. Power Query is designed to import and reshape data through a recorded sequence, then load the transformed output. For routine tasks such as combining monthly files or standardizing inconsistent columns, a query can be easier to repeat and audit than a long chain of manual edits.

Manual editing is reasonable for a one-time, small correction, but it is easy to miss a row or repeat a step inconsistently when the task returns every month. A PivotTable summarizes data for analysis, while Power Query prepares data for analysis; one does not replace the other. A common workflow is to use Power Query to clean and combine the source, then build a PivotTable from the loaded table.

There is no rule that every transformation belongs in Power Query. If a calculation needs to update interactively in a worksheet, a formula may be clearer. If you need a reusable import and cleanup process, Power Query is often a better fit. Some workbooks use both: queries produce a consistent table, and formulas or PivotTables calculate and present results from it.

Common problems, privacy, and practical limits

One frequent issue is a type conversion error. A column that looks numeric may contain text such as “N/A,” or dates may use a regional format different from the one Excel expects. Check the source values and the column’s type-setting step; changing locale-aware interpretation may be necessary when dates or decimal separators are ambiguous.

Other problems often come from a changed source: a renamed header, moved file, missing column, altered folder structure, or expired sign-in. Start with the error message and inspect the step where the query fails. If a source’s structure has changed, update the relevant step rather than deleting later steps blindly. For folder imports, keep unrelated files out of the folder or make the query filter them deliberately.

Power Query does not make source data private or secure by itself. Use only sources you are authorized to access, protect sensitive output in the workbook, and avoid sharing credentials. Excel provides data-source and privacy settings that influence how sources are accessed and combined; choose appropriate privacy levels rather than disabling protections just to silence a warning. Also remember that loading personal or confidential records to a shared worksheet may expose them to anyone with access to that workbook.

Frequently Asked Questions

Is Power Query Editor free in Excel?
Power Query is included in supported desktop editions of Excel, including Microsoft 365 Excel. You generally do not buy the editor separately, although access to a particular data source, service, or organizational connector may require its own account, permission, or license.

Does Power Query change my original file?
Ordinary query transformations shape the data returned to the query and do not rewrite the source file. The result is loaded into the workbook or retained as a connection. Treat source-editing tools and external systems separately, and keep a backup when working with important data.

Do I have to know M code to use the editor?
No. The interface creates M instructions for common tasks, so many users can build useful queries without writing code. M is helpful for advanced logic, reviewing what a query does, or customizing transformations that are awkward to express through the ribbon.

Why does Refresh fail?
Common causes include a moved or renamed source file, changed column names, unavailable network access, expired credentials, or a source format that has changed. Check the error details, confirm that the source is reachable, and inspect the Applied Steps around the point where the query stops working.

Can Power Query combine multiple files?
Yes. Excel can connect to a folder and combine files that share a compatible structure. This is useful for recurring exports, but the files should have consistent columns and formats. Review the generated sample-file transformation and filter out unrelated files before relying on the combined result.

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.