Power Query Basics: Import, Clean, and Transform Data

Power Query is Excel’s data-import and transformation tool: it connects to files, databases, and other sources, then lets you clean and reshape data through repeatable steps. In Excel for Microsoft 365 on Windows, start at Data > Get Data, choose a source, transform the preview in Power Query Editor, and load the result to a worksheet or the Data Model.

What Power Query is—and what it is for

Power Query is designed to prepare data before you analyze it. It can import information from sources such as Excel workbooks, text or CSV files, folders, databases, and selected online services. You then apply transformations—such as removing unwanted rows, splitting a column, changing data types, or combining tables—and load the prepared result into Excel.

The important idea is that a query records the instructions for preparing data, not just the final values. When the source changes, you can refresh the query and repeat those instructions rather than performing the same cleanup manually. This makes Power Query useful for recurring reports, monthly exports, and datasets that need consistent preparation.

Power Query does not normally edit the original source file. It reads the source and creates an output in Excel. This separation is valuable: the source remains intact, while the query provides a documented route from raw data to a usable table. It is not a replacement for every Excel feature, though. Formulas are useful for calculations that should respond to worksheet cells, and PivotTables are useful for summarizing data interactively.

The basic Power Query workflow

A typical workflow has four stages: connect, transform, load, and refresh. For example, imagine a monthly CSV export containing sales transactions. It has a report title above the column names, dates stored as text, and several columns that are irrelevant to the analysis.

Connect: In Excel for Microsoft 365 on Windows, select Data > Get Data > From File > From Text/CSV, choose the file, and inspect the preview. For an Excel workbook, choose Data > Get Data > From File > From Workbook.
Transform: Select Transform Data to open Power Query Editor. Remove the report-title row, promote the actual field names to headers, remove columns you do not need, and set the date and amount columns to suitable data types.
Load: Select Home > Close & Load to send the result to a worksheet. Use Home > Close & Load To when you want to choose a destination or load the query as a connection or to the Data Model.
Refresh: When the source export is updated, select Data > Refresh All. Excel reruns the saved steps and updates the query output, provided the source can still be reached and the connection is authorized.

The exact options shown depend on the source and Excel version. Excel for Mac includes Power Query capabilities in Microsoft 365, but supported connectors and authoring options can differ from Windows. Excel for the web also has a more limited experience than the Windows desktop app, so use the desktop app when you need the full set of authoring controls.

Inside Power Query Editor

Power Query Editor is a preview-and-transformation workspace. The center displays a sample preview of the data, while the ribbon contains transformation commands. The Queries pane lists the queries in the workbook, and the Query Settings pane shows the query’s name and its Applied Steps.

Each transformation generally appears as a step. Selecting a step lets you see the data as it looked at that point; you can often change, reorder, or delete steps. The order matters. For example, removing blank rows before changing a column’s type may behave differently from changing the type first. A step can also depend on a column name or structure that an earlier step changed, so edits sometimes cause later steps to show errors.

Power Query previews data, but a preview is not always the full result. Large sources may be sampled or processed in ways that make the preview differ in size from the final output. Check the column names, data types, and any error indicators, and validate the loaded result when accuracy matters. Renaming a query with a clear name such as MonthlySales is more helpful than leaving it as Query1.

Common transformations and a practical example

Most everyday cleanup involves a small set of operations. Power Query can filter rows, remove duplicates, sort data, replace values, split or merge columns, group records, pivot or unpivot columns, and add custom or conditional columns. It can also change data types, which is essential because dates, numbers, text, and logical values behave differently in later steps.

Suppose a sales export has one row per transaction, with columns for OrderDate, Region, Product, and Revenue. A useful preparation might remove rows with no order date, set OrderDate to the Date type, set Revenue to a decimal number, trim extra spaces from Region, and filter out test transactions. The resulting table is easier to summarize because each field has a consistent meaning and type.

Think carefully before removing duplicates. The command removes rows based on the columns selected, not on what a person considers a duplicate. If two orders have the same customer and date but different order IDs, removing duplicates using only customer and date could discard a legitimate transaction. Select the fields that define a unique record, or use a grouping operation when the goal is to summarize repeated records.

Append, merge, and combine files

Power Query can combine related data in two main ways. Append stacks rows from one table under another, much like adding another month of transactions to the bottom of a dataset. It is appropriate when the tables have the same kind of fields, even if their column order differs. Columns are matched by name; missing fields can result in blank values.

Merge joins tables side by side using one or more matching fields. For instance, merge a transactions table with a product lookup table using ProductID to add product names or categories. Before merging, check that the lookup key is consistent and that the lookup table does not contain unintended duplicate keys. Duplicate matches can increase the number of rows in the result.

For a repeated set of similarly structured files, the From Folder connector can combine files from a folder and its subfolders. In Excel for Microsoft 365 on Windows, start with Data > Get Data > From File > From Folder, choose the folder, then use the combine option in the preview. This is handy for monthly files, but it works best when files have the same layout. A changed column name, extra title row, or different file type may require adjustments to the combine steps.

Loading results and refreshing safely

Power Query output can be loaded as a table on a worksheet, kept as a connection, or added to the Data Model for use with PivotTables and other analysis. A worksheet table is straightforward to inspect and reference. A connection-only query can serve as an intermediate step for another query without creating another visible table. The Data Model can help when working with related tables or larger analytical models.

Refresh is not a guarantee that every query will update successfully. Excel needs access to the source, and some connections require credentials, permissions, or an available network. A file-based query can also fail if the file is moved or renamed. If other users need to refresh a workbook, consider whether they can access the same source path and whether the connector works in their Excel environment.

Refreshing replaces or updates the query output; it does not necessarily preserve manual edits made inside the loaded result. Treat a query’s output table as generated data. Put notes or corrections in a separate table and, when appropriate, merge them into the query using a stable identifier rather than typing changes into cells that refresh may overwrite.

How M and query folding fit in

Power Query stores transformation instructions in a language called M. Most users do not need to write M to use the tool: choosing commands in the editor creates the steps. The formula bar or Advanced Editor can expose the underlying expressions for users who want to inspect or edit them. M is useful for more specialized logic, but a typo or a change to a step’s structure can break later steps.

With some database connectors, Power Query can use query folding: it translates supported transformations into operations that run at the source rather than downloading all the raw data first. Folding can improve performance, especially for large tables. Not every operation or connector supports it, and a step may stop folding after a transformation the source cannot handle. For a database query, filtering early and selecting only necessary columns can reduce the amount of data to process, but results depend on the connector and source.

Privacy settings also matter when a query combines sources. Excel may apply data privacy protections that affect how sources are combined or evaluated. Do not lower privacy protections simply to make a warning disappear; check that the sources are trusted and follow your organization’s rules. If a query behaves differently after a source or credential change, review its connection and privacy settings as well as its transformation steps.

Power Query compared with formulas and alternatives

Choose Power Query when you repeatedly import and prepare data, especially when the cleanup involves several steps or multiple files. It makes the sequence visible and repeatable. Choose worksheet formulas when results need to react immediately to user-entered values or when calculations belong directly in the worksheet model. Use a PivotTable to summarize and explore already prepared data; Power Query prepares the data but is not itself a PivotTable reporting interface.

Power Query is included in current desktop versions of Excel, though available connectors and features can depend on the Excel edition, operating system, organization settings, and source. It is not the same thing as Power BI. Power BI uses Power Query for data preparation too, but it is a separate product with its own modeling, publishing, and sharing features. If all you need is a one-time cleanup of a tiny list, ordinary Excel commands may be quicker. If the task recurs, saving a query can prevent repetitive manual work.

Common problems and good habits

Dates or numbers appear wrong: Check the column’s data type and regional interpretation. A text value such as 03/04/2026 can be read differently depending on locale; verify whether the source means March 4 or April 3 before converting it.
A refresh reports a missing source: Confirm the file path, filename, permissions, and network availability. If the source moved, update the source step rather than rebuilding the whole query.
Rows disappear after a merge: Review the join type and matching values. Extra spaces, different capitalization, or different data types in key fields can prevent otherwise expected matches.
A step shows an error: Select the failing step and inspect the error details. A source column may have been renamed, removed, or changed to a type that cannot accept some values.
The output overwrites manual work: Keep manually maintained information outside the query output and combine it through a key-based merge where appropriate.

For maintainable queries, use descriptive names, remove unnecessary columns early, keep transformations understandable, and document assumptions such as which row contains headers or what makes a record unique. Test a refresh after changing the source structure. These habits make it easier for someone else to understand why the output looks the way it does.

Frequently Asked Questions

Is Power Query included in Excel?
Power Query is integrated into modern desktop Excel, including Excel for Microsoft 365. The available connectors and features vary by platform and version; Excel for Windows generally offers the broadest authoring experience.

Does Power Query change my original data?
Normally, no. It reads from a source and loads a transformed result elsewhere. The source file or database is not edited by the query’s usual import and transformation process.

Do I have to learn M to use Power Query?
No. You can build many common queries by selecting commands in Power Query Editor. M is the language behind the steps and becomes useful when you need to customize logic beyond the standard interface.

How do I update Power Query results?
Use Data > Refresh All in Excel to refresh workbook connections. The source must be available, and credentials or permissions may be required.

What is the difference between append and merge?
Append adds rows from one table to another. Merge matches rows using selected key columns and adds fields from the matching table, similar to a database join.

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.