What Is Power Query? Excel Data Cleanup Explained

Power Query is Microsoft Excel’s data-import and transformation tool: it connects to files, databases, and other sources, then lets you clean and reshape their data before loading the results into a worksheet or the Data Model. Instead of repeating the same cleanup by hand, you can save the steps as a query and refresh them when the source data changes.

What Power Query does

Power Query helps move data from its original location into a form that is easier to analyze. The general process is often described as extract, transform, and load: extract data from a source, transform it to suit your needs, and load the result somewhere useful. You do not need to write code for many common tasks; the Power Query Editor provides menus and buttons for operations such as removing columns, filtering rows, splitting text, and changing data types.

For example, suppose a team receives a separate sales file each month. The files contain the same columns, but also include blank rows, inconsistent date formats, and columns the team does not need. A Power Query can connect to the files, retain the useful fields, set the date and number types, remove irrelevant rows, and combine the records. Once the next monthly file is available in the expected location and format, refreshing the query can repeat those steps.

The result is not a live view that automatically changes every second. A query generally reads its source when it is refreshed, then updates its output. This makes it useful for repeatable reporting, but it also means that a workbook can show older results until someone refreshes it.

Where Power Query fits in Excel

In Excel for Microsoft 365 on Windows, Power Query is built into the Data tab under the Get & Transform Data area. Common starting points include Data > Get Data and Data > From Table/Range. The exact options shown depend on the source and the Excel build. Choosing a source or opening an existing query brings up the Power Query Editor, where you preview and transform the data.

Power Query is part of the broader Microsoft data-analysis environment. It is available in Excel and is also used in Power BI and other Microsoft products, although available connectors and features can differ between applications. Power Query is not the same thing as Power Pivot: Power Query prepares and loads data, while Power Pivot is used to build relationships and analyze data in a model. Excel’s Data Model can connect these parts of a workflow.

Support varies by platform. Excel for Mac includes Power Query capabilities, but its connectors and editing features are not identical to Excel for Windows. Excel for the web also supports some query workflows, but it does not necessarily provide the same connector coverage or full authoring experience as the Windows desktop app. If a workbook depends on a particular connector or transformation, check that it is supported on the platform where the workbook will be refreshed.

How a query works

A query is a saved set of instructions. When you connect to a source, Power Query typically shows a preview and records the actions you take as applied steps. Examples include selecting a worksheet, promoting the first row to headers, filtering out records, and changing a column’s type. The order matters: later steps operate on the result of earlier ones.

When you refresh, Power Query runs the instructions against the source again. It does not normally save a separate manual instruction for every row. If a new row appears in a source table, a query based on that table can usually include it after refresh, provided the source and steps still match. A step that expects a column named “Amount,” for instance, may fail if someone renames that column in the source.

To start a basic query from a worksheet range, first make the range an Excel table if it is not one already. Select a cell in the range, choose Insert > Table, confirm the range and headers, and then use Data > From Table/Range. In the editor, make the required changes, then choose Home > Close & Load or use the Close & Load To option to choose a destination. Menu labels can vary slightly by build.

Useful transformations and examples

Power Query is particularly helpful when the same kinds of changes must be applied consistently. Typical transformations include:

Filtering and removing: Keep records that meet a condition, remove blank rows, or drop columns that do not belong in the report.
Splitting and combining text: Separate a full name at a delimiter, or combine fields such as a city and region into a new value.
Changing data types: Interpret a field as a date, whole number, decimal, or text so that later calculations and sorting behave as intended.
Appending queries: Stack records from similarly structured tables, such as monthly files, one below another.
Merging queries: Match records from two tables using one or more related columns, much like joining tables in a database.
Pivoting and unpivoting: Reshape data. Unpivoting, for example, can turn separate columns for January, February, and March into rows with a month field and a value field.

Consider a report that arrives with one column per sales region. If the next report adds another region, a manually prepared chart source may need adjustment. Unpivoting the region columns can instead create a consistent structure with fields such as Product, Region, and Sales. That long format is often easier to filter, summarize, and use in a PivotTable.

When combining files from a folder, consistency matters. Files should have compatible column names and layouts, and unrelated files should not be mixed into the same folder. If one file has an extra heading row or a different sheet name, the combine process may need additional steps to handle that variation.

Loading results and refreshing data

After transforming data, you can load it as a table on a worksheet or into the workbook’s Data Model. Loading to a worksheet is convenient when people need to inspect the prepared rows directly. The Data Model is useful when the dataset is too large or when you want to relate multiple tables for analysis, though it requires understanding model relationships and reporting tools such as PivotTables.

To update results, use Data > Refresh All, or right-click a query output table and choose Refresh. Excel also offers refresh settings for queries; the options depend on the query and workbook. A refresh can fail if a file has moved, a database is unreachable, credentials have expired, or the source’s structure has changed. Refresh is a deliberate retrieval process, not a guarantee that every source updates instantly or that a workbook refreshes successfully without access.

Queries can be set up as connection-only rather than loaded to a visible worksheet, which is useful when one query feeds another or when data goes into the Data Model. This helps avoid unnecessary duplicate tables. A sensible workbook should make clear which outputs are intended for users and which queries are intermediate preparation steps.

Applied steps and the M language

Each transformation appears in the Applied Steps list. Selecting a step lets you inspect the data at that point; you can often rename, edit, reorder, or delete steps. This step-by-step structure makes a query easier to review than a long sequence of undocumented manual edits. However, changing an early step can affect every step after it, so check the preview and refresh after significant edits.

Power Query uses a language called M behind the graphical interface. Many actions taken in the editor generate M code automatically, so ordinary users can build useful queries without learning the language. More advanced users can inspect or edit code through the formula bar or Advanced Editor. M is distinct from Excel worksheet formulas and from the DAX language used for measures and calculated columns in data models.

Understanding that M exists is useful even if you never write it directly. It explains why queries can be reusable and why the editor records transformations as instructions. It also helps when troubleshooting: an error may identify a particular step or expression, pointing to a mismatch between what the query expects and what the source now contains.

Common problems and practical safeguards

A common misconception is that Power Query automatically fixes every data-quality problem. It can apply explicit cleaning rules, but it cannot reliably infer what a business value was supposed to mean. If a column contains both dates and notes, for example, changing its type to Date may create errors for the text entries. Review errors rather than simply removing them, especially if they could represent important records.

Other frequent issues include:

Changed source paths: A query that points to a local file may stop working after the file is renamed or moved. Store shared sources in a stable, accessible location and update the connection when needed.
Unexpected types or regional formats: Dates and decimal separators can be interpreted differently depending on locale. Confirm the column type and, where necessary, use the appropriate locale when converting values.
Credential or privacy prompts: Connections may require authentication, and Excel may ask how to handle privacy levels when combining sources. Use authorized credentials and follow your organization’s data policies.
Source layout changes: Renamed headings, deleted worksheets, or added title rows can break steps that rely on the previous structure. Agree on a stable source layout when possible.
Slow refreshes: Large sources and complex transformations can take time. Filter or remove unnecessary data early where appropriate, and avoid loading redundant copies.

Be careful with sensitive data. A query does not make a source safe to share, and loading data into a workbook can create another copy of it. Check who can access the workbook, where source files are stored, and whether organizational rules allow combining or distributing those sources.

When to use Power Query instead of alternatives

Use Power Query when the task is mainly importing, cleaning, combining, or reshaping data and you expect to repeat it. It is especially valuable when the source is external or when manual preparation would otherwise be performed every reporting cycle. It can also make the transformation process more auditable because the steps are saved and can be reviewed.

Worksheet formulas are often better for calculations that need to update immediately as users edit cells, or for a small, transparent task inside a worksheet. PivotTables summarize data after it is available in Excel; they do not replace a repeatable process for importing and cleaning several source files. VBA can automate broader Excel actions, but it requires code and macros may be restricted by security policies. For database-scale processing or centrally managed data pipelines, a database or dedicated data platform may be more suitable.

These tools can work together. For example, Power Query can import and standardize sales records, load them into the Data Model, and a PivotTable can summarize revenue by product and month. The right choice depends on where the data lives, how often it changes, how much it contains, and who must maintain the process.

Frequently Asked Questions

Is Power Query free in Excel?
Power Query is included in supported Excel editions, including Microsoft 365 desktop Excel, rather than being a separate paid add-in for those users. Access to Excel itself and some connected services may require a license, and availability differs by platform and edition.

Does Power Query change the original data?
In a typical Excel workflow, Power Query reads the source and loads transformed results into the workbook; it does not edit the source file. Changes you make in the query affect the prepared output, not the original source records.

Is Power Query the same as a PivotTable?
No. Power Query imports and transforms data. A PivotTable summarizes data that is already available in Excel or the Data Model. A common workflow uses Power Query to prepare records and a PivotTable to analyze them.

Does Power Query refresh automatically?
Not necessarily. You can refresh manually, and some query refresh settings can control refresh behavior, but refresh still depends on the source being available and any required credentials working. Check the workbook’s refresh settings and verify that the updated output is correct.

Do I need to learn M to use Power Query?
No. The editor’s interface covers many everyday transformations and creates the M instructions for you. Learning some M can help with advanced logic and troubleshooting, but it is optional for basic importing, filtering, combining, and reshaping.

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.