Power Query Data Transformation in Excel: A Practical Guide

Power Query data transformation is the process of importing data into Excel, cleaning and reshaping it in the Power Query Editor, then loading the result for analysis. In Excel for Microsoft 365 on Windows, start from Data > Get Data or select a range and choose Data > From Table/Range, make the needed changes, and select Home > Close & Load.

What Power Query does

Power Query is Excel’s built-in tool for connecting to data and preparing it for use. It can read sources such as workbooks, text and CSV files, folders, databases, and some online services. Instead of manually editing each new export, you create a sequence of transformation steps and refresh the query when the source data changes.

The result can be loaded to an Excel worksheet table or, where appropriate, to the workbook’s Data Model. A query can also be kept as a connection without placing its output in a worksheet. This separation is useful: the source remains the input, the query describes how to prepare it, and the loaded result is the output.

Power Query is not a spreadsheet formula and it does not normally edit the original source file. It creates a prepared version of the data in Excel. That makes it useful for recurring tasks such as standardizing monthly sales exports, removing unwanted rows, or combining files with the same layout.

How a query works

When you connect to a source, Excel opens the Power Query Editor, where you can inspect a preview and apply transformations. Each action—such as filtering rows or changing a column’s type—is recorded as an applied step. The sequence is visible in the editor, so you can review, rename, reorder, or remove steps rather than repeat the entire cleanup by hand.

When you refresh, Power Query reads the available source data and evaluates the steps again. For example, a filter that keeps rows where Region is West will generally apply to newly added rows too, provided the source and its structure remain compatible. The query’s output may therefore change after refresh, even though you did not edit the steps.

Power Query uses the M language behind the scenes to describe transformations. Most everyday tasks can be done with the interface; the editor generates the M code for you. People who need more control can view or edit that code, but learning M is not a prerequisite for basic cleaning and combining.

A typical transformation workflow

For a repeatable task, work from a stable source and make each transformation explicit. In Excel for Microsoft 365 on Windows, a common workflow is:

Connect: Choose Data > Get Data and select the source type, such as From File > From Workbook or From Text/CSV. For a well-formed Excel range, select a cell in it and choose Data > From Table/Range.
Inspect the preview: Check that headers, rows, and columns were detected correctly. If the first row contains field names, use the command to promote headers when necessary.
Clean and reshape: Remove irrelevant columns, filter unwanted records, split combined fields, or replace inconsistent values.
Set data types: Assign appropriate types, such as date, whole number, decimal number, or text. Verify that dates and decimal separators were interpreted as intended.
Load and verify: Choose Home > Close & Load, then check the resulting table against a few known source records.
Refresh later: Use Data > Refresh All to update workbook queries when the source data changes.

For a one-off cleanup, loading the result may be all you need. For a recurring report, save the workbook and document where the source file lives, what shape it should have, and which output table or report depends on the query.

Common transformations and examples

Power Query is especially helpful when the same kinds of imperfections appear repeatedly. Suppose a monthly export has columns for Order ID, Customer, Date, and Amount, plus blank rows and extra fields that are not used in a report. A query can remove blank records and unnecessary columns, set Date and Amount to the correct types, and filter out test orders. When the next month’s export follows the same structure, refreshing can apply those steps to the new data.

Other useful operations include:

Filter and sort: Keep only relevant dates, regions, or statuses, or arrange records for inspection.
Split or combine columns: Separate a full name at a delimiter, or create a combined text field from existing columns.
Replace and clean values: Standardize labels such as N, North, and NORTH, or remove extra spaces.
Group and summarize: Aggregate rows by a key, such as calculating total sales by product or month.
Pivot and unpivot: Turn category values into columns, or convert a wide table with one column per month into a longer list of month-and-value records.

Unpivoting is a particularly useful example of reshaping. A table with columns for January, February, and March can be converted into columns named Month and Sales. That long format is often easier to filter, chart, or combine with other records. Choose transformations based on the shape required by the next analysis, not simply on what makes the preview look tidy.

Combining files and tables

Power Query can append tables, which stacks rows from similar tables, or merge tables, which matches records using one or more key columns. Appending is appropriate for monthly files with the same fields. Merging is useful when, for example, an orders table needs customer details from a separate customer table. Check that the matching columns use compatible types and that the key is sufficiently consistent; mismatched spelling or duplicate keys can produce missing or repeated matches.

For a folder of recurring files, Excel’s folder connector can combine files that share a compatible structure. In Windows, choose Data > Get Data > From File > From Folder, review the listed files, and use the combine option. Keep unrelated files, temporary copies, and prior output files out of that folder, or they may be included in the result. If column names or layouts vary between files, inspect the combine steps and address the differences rather than assuming every file was interpreted correctly.

Refresh, loading, and Excel versions

Refresh reruns the query; it does not guarantee that every source can be reached or that the source still has the expected structure. A local file may have moved, a database may require new credentials, or a service may be unavailable. In Excel, Data > Refresh All refreshes workbook connections and queries, but availability and behavior can vary by connector and source.

Where supported, connection properties can control options such as refreshing when the workbook opens or refreshing at an interval. These settings do not make every source update continuously, and they may depend on the source, connection type, and Excel environment. Refreshing also does not automatically fix a changed file layout; a renamed or deleted column can break a later step.

The instructions here target Excel for Microsoft 365 on Windows. Excel for Mac has Power Query features, but connector support, editing capabilities, and menu locations can differ by version. Excel for the web also has a different feature set. If a command or connector is missing, check the current Microsoft support information for that platform and update channel rather than assuming the Windows steps are identical.

Common problems and how to diagnose them

A query error is often caused by a source change or a type conversion, not by the whole workbook being corrupted. Review the first applied step that shows an error and compare its assumptions with the incoming data. These checks resolve many routine issues:

Wrong date or number values: Confirm the column’s data type and regional interpretation. Text such as 03/04/2026 can mean different dates in different locales.
Missing rows: Inspect filters, removed duplicates, and source navigation steps. A filter may exclude records that were not present when the query was first created.
Missing columns after refresh: Check whether the source renamed, removed, or moved a field. Update the affected steps and verify the output.
Duplicate results after a merge: Check whether the lookup table has more than one row for a key. A match to multiple records can multiply output rows.
Access or privacy prompts: Confirm the source path, credentials, and privacy settings. Do not lower privacy protections simply to dismiss a prompt without understanding the data sources involved.

It is sensible to test a query on a small sample and compare totals or record counts with the original source. If a transformation removes records, identify the rule that removed them. A clean-looking table is not proof that the result is complete.

Power Query compared with formulas and other tools

Formulas are often better when a calculation needs to respond immediately to individual cell edits or when the result belongs directly in a worksheet model. Power Query is usually a better fit for repeatable import and cleanup tasks, especially when there are many rows or several files to combine. The tools can work together: load a prepared table with Power Query, then use formulas or PivotTables for analysis.

A PivotTable summarizes data that is already available; it is not primarily an import-and-cleanup tool. VBA can automate workbook actions, but it requires code and can be harder to maintain or audit. Power Query offers a visible sequence of data-preparation steps and is generally a practical first choice for recurring transformations. It is not a replacement for a database when data volume, shared access, governance, or performance requirements call for a dedicated data system.

Data access and safe use

Queries can contain paths, connection details, and transformation logic, while refresh may use credentials associated with the data source. Treat the workbook and its connections as part of the data workflow: share them only with appropriate people, store source files in controlled locations, and avoid embedding sensitive data in a workbook that will be distributed broadly. Power Query does not grant permission to data that a user is otherwise unauthorized to access.

Before replacing a published report or relying on refreshed results, verify that the query ran successfully and that key totals and row counts are plausible. Keep a copy of important source files or a versioned output when the transformation has business consequences. This is a data-quality precaution, not a guarantee that the query will preserve a historical snapshot.

Frequently Asked Questions

Is Power Query part of Excel?
Yes. Power Query is integrated into supported editions of Excel, including Microsoft 365 for Windows. The exact connectors and features depend on the platform, Excel version, and update channel.

Does Power Query change the original data?
Normally, no. It reads a source and loads a transformed result into Excel or a connection. Editing the loaded output does not usually rewrite the source file, and a later refresh may replace the query output with newly generated results.

Do I need to learn M to use Power Query?
No. The interface records common steps and creates the M language expression behind them. M becomes useful when a transformation is difficult to express through the menus or when you need to inspect and customize query logic.

Can Power Query refresh automatically?
Some connections offer refresh-on-open or scheduled interval settings through connection properties, but support depends on the source and Excel environment. For a manual update, use Data > Refresh All and check for errors.

What is the difference between append and merge?
Append stacks rows from tables with similar columns. Merge matches rows across tables using key fields, much like a database join. Choose append for more records of the same kind and merge when you need related fields from another table.

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.