Power Query: What It Contains and How It Works

Power Query contains data connectors, a visual editor for importing and transforming data, and the M language that records those transformations as repeatable steps. In Excel for Microsoft 365 on Windows, you use it to connect to sources, clean or reshape data, and load the result into a worksheet or the Data Model.

What Power Query is

Power Query is Microsoft’s data connection and preparation technology, often described as an extract, transform, and load tool. “Extract” means retrieving data from a source; “transform” means changing its structure or contents; and “load” means sending the prepared result to a destination. The point is to make these operations repeatable: instead of cleaning the same export manually each month, you can refresh a query and have its saved steps run again.

In Excel, Power Query is part of the Get & Transform Data experience. It is not a spreadsheet formula, a database, or a permanent copy of every connected source. A query describes how to retrieve and prepare data. Depending on the load choice, Excel can place the output in a worksheet, add it to the workbook Data Model, or keep a connection without displaying a table.

The main parts Power Query contains

Power Query is made up of several connected pieces rather than one feature. The most useful components to recognize are:

Connectors: options for bringing in files, databases, online services, and other data sources.
Power Query Editor: the interface for previewing data and applying changes.
Queries and steps: saved instructions describing the source and the sequence of transformations.
M language: the formula language Power Query uses to express those instructions.
Refresh and load controls: features that rerun a query and send its output to a supported destination.

These parts work together. A connector establishes the source, the editor helps you define the preparation, M represents the logic, and refresh runs that logic again. The result is then loaded according to the settings you chose.

Connectors and data sources

Connectors are the entry points to data. Depending on the Excel version, organization settings, and available access, sources can include text and CSV files, Excel workbooks, XML or JSON files, folders, databases, and online services. Common Excel options are available from Data > Get Data. The choices shown can vary, so a connector available in one installation or product may not appear in another.

Power Query can also combine data from more than one source. For example, a monthly sales workbook might be combined with a product lookup table, or files in a folder might be appended into one table. Combining sources does not automatically make their columns compatible: you may still need to set data types, align column names, or remove unwanted rows.

A source connection can require credentials or network access. A query may store or use connection information according to the source and application settings, but it does not bypass permissions. If a file is moved, a database is unavailable, or an account loses access, refresh can fail even though the query steps remain in the workbook.

What is in the Power Query Editor

The Power Query Editor is where you inspect and shape the data before loading it. Its preview shows a sample of the query result, with columns and rows that help you identify issues such as incorrect headers, unexpected blank rows, or dates interpreted as text. The preview is a working view, not necessarily a guarantee that every row in a very large source is displayed at once.

The editor’s ribbon includes commands for common tasks. The Home tab includes actions such as closing and loading, managing columns, and combining queries. Transform contains operations that change selected data, while Add Column creates new columns from existing information or expressions. Options on View control elements such as the formula bar and data profiling displays.

The editor also has a Queries pane for navigating between queries and a Query Settings pane. In Query Settings, Applied Steps lists the operations already performed. Selecting a step lets you inspect the result at that point in the sequence; some steps can be renamed, reordered, or deleted. A change early in the sequence can affect later steps, so removing a step may cause errors downstream.

Transformations, steps, and the M language

Power Query’s transformations include filtering rows, sorting, removing or renaming columns, changing data types, splitting text, replacing values, grouping rows, and merging or appending tables. A merge matches rows using one or more fields, much like a lookup or relational join. An append stacks rows from tables with compatible columns. These operations are recorded as steps rather than applied as an untracked series of edits to the original source.

Behind the visual interface, a query is expressed in M, also called the Power Query Formula Language. Each applied step generally refers to the result of an earlier step and produces a new result. You do not have to write M for routine work: selecting commands in the editor creates the logic. The formula bar can show the expression for a selected step, and Home > Advanced Editor opens the query’s M code for inspection or editing.

M is useful when a built-in command is not enough or when you want to understand exactly what a query does. It is distinct from Excel worksheet formulas such as SUM or XLOOKUP. Editing M directly requires attention to syntax and step names; a small change can alter how later steps behave. For many users, the visual steps are easier to maintain, while the generated M remains available for debugging.

Loading results and refreshing queries

After shaping data, use Home > Close & Load to return to Excel, or the load options to choose how the query is stored. A query can load as a table in a worksheet, be added to the Data Model for analysis with PivotTables and related tools, or be set up as a connection only. The right choice depends on whether you need visible rows, a model for analysis, or an intermediate query used by another query.

Refreshing reruns the query against its source and updates the loaded result when the connection and permissions allow it. In Excel, use Data > Refresh All to refresh workbook connections, or open Data > Queries & Connections to manage queries and refresh an individual one. Refresh is not the same as a live link that instantly changes every time the source changes: the result updates when a refresh occurs, whether initiated manually or through configured refresh settings.

Refreshing can also replace the current query output. If someone has typed notes directly into cells inside a loaded table, those notes may not remain aligned with the refreshed data. Keep manual annotations in a separate table or worksheet and connect them by a stable key where appropriate.

Query folding, privacy, and common problems

With some structured sources, Power Query can translate eligible transformation steps into instructions that the source system performs. This is called query folding. It can reduce the amount of data transferred and improve performance, but it depends on the connector, source, and operations used. Some steps may prevent later operations from folding. A query can still work without folding; it may simply need to process more of the data in Power Query.

Power Query also has privacy controls for data sources. When a query combines sources, privacy levels help determine how data may be shared between them during evaluation. Choosing an inappropriate privacy setting can create a data-exposure risk, while restrictive settings may limit combinations or cause prompts. Follow your organization’s policy and use the level that accurately reflects each source rather than changing settings just to suppress a warning.

Common errors usually point to a change in the source or in access conditions. A renamed column can break a later step that expects the old name; a new file format or changed folder path can invalidate the source; and a credential prompt may appear after a password or permission changes. To investigate, open the query, select the first step that shows an error, and check the source path, column names, data types, and credentials. Diagnosing the earliest failing step is often more useful than focusing only on the final error message.

Power Query in Excel and Power BI

Power Query is used in both Excel and Power BI, but the surrounding products differ. In Excel, the usual destination is a worksheet table or the workbook Data Model. In Power BI Desktop, prepared data is typically loaded into the Power BI model for reports. The editor and M language have substantial similarities, but connectors, available features, refresh arrangements, and destination options are not identical across products.

These differences matter when a workbook is shared with someone using another platform. Excel for Mac and Excel for the web have different or more limited Power Query capabilities than Excel for Microsoft 365 on Windows, and connector availability and editing support can vary by release. If a task depends on a particular connector or advanced transformation, check that it is supported in the recipient’s version before designing a workflow around it.

Frequently Asked Questions

Is Power Query included in Excel?
Power Query is built into modern Excel versions as part of Get & Transform Data. The exact connectors and editing features vary by platform and release. In Microsoft 365 Excel for Windows, start with Data > Get Data.

Does Power Query change the original source file?
Normally, Power Query reads from the source and creates a prepared result; its transformations do not edit the original file. The output is loaded separately into Excel or another supported destination. Avoid treating a loaded table as a substitute for the source data.

Do I need to learn M to use Power Query?
No. You can perform many everyday tasks through the editor, which records the steps in M for you. Learning to read M can help with debugging and more advanced customization, but it is not a prerequisite for importing and cleaning typical tables.

Is Power Query the same as Power Pivot?
No. Power Query is primarily for connecting to and preparing data. Power Pivot refers to Excel’s Data Model and its modeling and analysis capabilities. A common workflow is to prepare data in Power Query and then load it into the Data Model for analysis.

Why does a Power Query refresh fail?
Common causes include a moved or renamed source, changed column names, expired credentials, missing permissions, an unavailable network location, or a transformation that no longer fits the incoming data. Check the earliest failing applied step and verify the source and access first.

Is Power Query a database?
No. It is a tool for connecting to and preparing data, not a database for storing and managing records. It can read from databases, among many other sources, and load results into a worksheet or data model.

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.