What Is Power Query in Excel? How to Clean and Shape Data Step by Step

Power Query is Excel’s built-in extract, transform, and load (ETL) engine, found under Get & Transform on the Data tab. It lets you import data, clean and reshape it through a visual editor, and save every change as a recorded step written in the M language. When new data arrives, you click Refresh and Power Query replays those steps without touching the source file. This guide walks through a typical cleanup, explains when Power Query is the right tool, and lists the mistakes that most often break a query.

What is Power Query in Excel?

Power Query is Excel’s built-in tool for extracting, transforming, and loading data, also known as Get & Transform. You can use it to clean a recurring CSV export without changing the source file. Power Query saves your changes as steps that run again when you refresh the data.

In the Power Query Editor, each action appears in the Applied Steps list. For example, you can turn the first row into column headers, set a date column’s data type, and remove duplicate records. When you refresh, Power Query reads the source again and applies those steps in order to produce updated results.

How to clean and shape data in Power Query, step by step

Step 1: Import a CSV or folder of files

For one CSV, open Excel’s Data tab and select Get Data > From File > From Text/CSV. Choose the file, then select Transform Data to inspect it in the Power Query editor before loading it into Excel.

For several CSVs with the same layout, select Get Data > From File > From Folder and choose the folder. Select Combine & Transform Data. Power Query builds the combined query using a sample file and creates a Transform Sample File query. If each file starts with an extra title row, remove that row and promote the next row to headers in Transform Sample File. Power Query then applies those changes to every file. Check the combined preview for inconsistent columns. When you add another file to the folder, Refresh includes it in the output.

Step 2: Promote headers and set data types

Promote the header row before setting data types, so later steps can refer to the right columns. If the preview shows Column1 and Column2 above your actual field names, select Use First Row as Headers. If a report title sits above the field names, remove that row first.

Next, use the type icon beside each column header to set dates to Date and quantities to Whole Number. Keep identifiers such as ZIP codes as Text so Power Query preserves leading zeros. Set types early because later filters and calculations depend on them. If Power Query misreads a day-first date such as 31/12, choose Change Type > Using Locale and select the source file’s regional format. Check the preview for errors before continuing.

Step 3: Split columns and remove duplicates

Split a column when one field contains values you need separately. For example, select a column containing West-042, then choose Transform > Split Column > By Delimiter and use the hyphen to separate the region from the code. If every value follows a fixed-width pattern, choose By Number of Characters instead.

Remove duplicates only after you decide which columns define a unique record. Select those columns, then choose Home > Remove Rows > Remove Duplicates. Power Query keeps the first matching row, so selecting only a customer name could discard legitimate orders from the same customer. Use column filters to exclude blank or test records before deduplicating, or use Keep Top Rows when you need to remove rows outside a known range.

Step 4: Unpivot, merge, and append queries

If your sales file has a separate column for each month, unpivot it before combining it with other data. Select the identifier columns, such as Customer and Product, then choose Transform > Unpivot Columns > Unpivot Other Columns. Power Query turns the month headers into rows. Rename the resulting Attribute and Value columns to Month and Sales so each row identifies one customer, product, month, and sales amount. The Power Query cleaning guide shows this menu sequence.

Use Append when two queries contain the same kind of records. For example, Home > Append Queries stacks January and February sales files into one table. Check that their column names and data types match so the values land in the intended columns.

Use Merge when you need fields from a different table. Home > Merge Queries can match sales rows to a customer table using Customer ID. A Left Outer join keeps every sales row, while an Inner join keeps only matches. A Left Anti join shows sales rows without a matching customer, which helps you inspect missing IDs. After the merge, expand the new column to select the customer fields you need.

Step 5: Load to a table or the Data Model

Choose where Excel should put the cleaned data. Close & Load creates a worksheet table you can inspect and use in formulas. If you plan to analyze a large result with PivotTables or Power Pivot, choose Close & Load To, then Only Create Connection and Add this data to the Data Model instead of filling a worksheet with the rows. Power Query’s load options let you make that choice for each query.

When the source file changes, use Refresh. Power Query reruns the saved transformation steps and updates the loaded result without requiring you to repeat the cleaning.

When to use Power Query vs. formulas, pivot tables, Power BI, or Python

Use Power Query when you repeat the same cleanup on incoming data. If a new sales CSV arrives each week with inconsistent dates and separate month columns, you can save the cleanup steps and rerun them on refresh. Power Query records those steps without changing the source files.

Use formulas for a quick lookup or a calculation tied to worksheet cells. An XLOOKUP that fills in a few missing product names may be simpler than building a query. If you keep copying formulas across new exports and fixing broken ranges, Power Query gives you a repeatable way to prepare the data.

Use a PivotTable when your data is already clean and you want to summarize it by category, date, or another field. A PivotTable can total sales by region, but it will not repair inconsistent region names in the source. You can clean the names in Power Query, then build a PivotTable from the loaded results.

Choose Power BI when you need to publish interactive reports for other people and manage refresh outside an open Excel workbook. Choose Python with pandas when the preparation calls for custom code, other Python libraries, or a larger scripted workflow. Power Query remains a practical starting point when the work fits in Excel and you want to inspect each cleanup step before refreshing it. For a broader view of where each tool fits, see Excel vs Power BI: which to learn first, and when your cleanup outgrows Excel, the same ideas carry over to data cleaning in Python with pandas.

Common Power Query mistakes

• Keeping columns you will never use. Remove them early so later steps process less data. Power Query cleaning guidance recommends trimming unneeded columns near the start.

• Setting data types too late. Set dates and numbers before transformations that depend on them. A date stored as text can cause a later step to fail or produce the wrong result.

• Merging tables before you summarize them. If you only need totals by customer, group the sales rows first. The merge then handles a smaller table.

• Skipping row-count checks after a merge. Duplicate keys can multiply rows, while mismatched keys can leave blank fields. Compare counts before and after the join.

• Relying on column positions. Select columns by name when building steps. A source file with reordered columns can otherwise break a query or change its output.

FAQ

Is Power Query free with Excel?

Power Query comes with supported desktop versions of Excel for Windows and Mac, so you do not buy it separately. You still need access to Excel, which may require a paid license or subscription. Menus and available features can vary by version and platform.

Does Power Query change my source data?

No. Power Query reads the source and records your cleaning actions as applied steps. When you refresh, it runs those steps again and updates the loaded result without editing the original file. If someone changes the source file, the refreshed result may change too.

What’s the difference between Power Query and Power Pivot?

Power Query prepares data before you analyze it. You can use it to clean columns, combine files, and load the result into Excel’s Data Model. Power Pivot works with data in that model, letting you create relationships between tables and calculations for analysis.

Build the habit of repeatable cleaning

Power Query teaches the same discipline analysts use everywhere: clean data with steps you can rerun, check row counts after every join, and keep the source untouched. The Data Analytics Course is an 11-week live program with one class per week and about 6 to 8 hours of weekly work, covering data foundations, SQL, Python and exploratory analysis, visualization, statistics, experimentation, and AI-assisted analytics before a final capstone. Developers who want to build LLM-powered applications can look at the engineering track instead.

• Data Analytics Course

• AI Engineering Course