# Power Query for repeatable data cleaning

Source: https://sarang.pro/power-query-repeatable-data-cleaning/
Author: Sarang | Published: 25 October 2026

If you clean the same spreadsheet every Monday, you are doing the same work fifty times a year, and each time is another chance to miss a step. Power Query exists to stop that. You clean the data once, Power Query records each step, and next week you press Refresh and the steps run again on the new data.

This guide explains what Power Query is, the four stages Microsoft describes, the handful of transformations that cover most business clean-ups, and the mistakes that make a query break on refresh. It draws on Microsoft's own documentation throughout.

## What Power Query is
Microsoft's [Power Query overview](https://learn.microsoft.com/en-us/power-query/power-query-what-is-power-query) describes it as a data transformation and preparation engine. It has a graphical interface for getting data from sources and an editor for applying changes to it. It does not store data itself. The host product, such as Excel or Power BI, decides where the results land.

In Excel the feature is also called Get & Transform, and its commands sit in the Get & Transform Data group on the Data tab. According to Microsoft's page [About Power Query in Excel](https://support.microsoft.com/en-us/excel/about-power-query-in-excel), it is available in Excel for Windows, Mac and the web, though not in Excel 2016 or 2019 for Mac. The same engine also runs in Power BI Desktop and in several online Microsoft services, so the skill carries over.

Two properties make it suitable for repeatable work. First, it does not change your source. A query is a new view of the data, and the original file stays as it was. Second, each change you make is saved as a step, and the full set of steps is saved as a query you can refresh.

## The four stages: connect, transform, combine, load
Microsoft breaks the work into four stages.

- Connect. Point Power Query at the data: a workbook, a CSV file, a folder of files, a database or an online service.
- Transform. Shape the data. Remove columns, filter rows, change data types, split columns, replace values, trim text.
- Combine. Append queries to stack rows from several tables, or merge queries to join two tables on a shared column, similar to a lookup.
- Load. Send the result to a worksheet or to the Data Model with Close & Load, or choose Load To for other options.
When you refresh, Excel brings in additions, changes and deletions from the source and runs every step again.

## Applied Steps: the recipe you can read
Every change you make in the Power Query Editor appears in the Applied Steps list in the Query Settings pane. You can click any step to see the data at that point, rename steps, delete them or insert new ones. Behind the scenes, Power Query writes each step in its own formula language, called M. You can view and edit the code in the Advanced Editor, but you do not need to read M to use Power Query well.

Treat the Applied Steps list as documentation. Rename steps to say what they do, such as "Removed test orders" instead of "Filtered Rows1". The next person to open the file, possibly you in six months, will understand the logic without guessing.

## Transformations that cover most business clean-ups
A small set of operations handles most real work.

- Change type. Set each column to text, whole number, decimal or date. Do this deliberately, because an automatic guess can be wrong.
- Remove columns and filter rows. Drop what you do not need early, so later steps run on less data.
- Trim, clean and change case. The same text fixes you would do with Excel formulas, recorded once.
- Split column. Break a combined field by a delimiter or by number of characters.
- Replace values. Standardise spellings, such as "UK", "U.K." and "United Kingdom".
- Unpivot. Turn a wide table with one column per month into a long table with one row per month.
- Group by. Summarise rows, for example total sales by region.
Unpivot deserves a closer look because reports from finance systems often arrive wide. Microsoft's [unpivot documentation](https://learn.microsoft.com/power-query/unpivot-column) explains that it creates two columns, Attribute and Value, and keeps the columns you leave alone as identifiers. There are three versions. Unpivot other columns, where you select the columns to keep, is the one Microsoft recommends when the number of columns may change, because new columns are picked up and unpivoted on refresh. Unpivot only selected columns does the opposite and leaves any new columns as they are. If next month's report adds a column for a new month, that choice decides whether your query keeps working.

## Combining a folder of files
A common use is merging monthly or branch files into one table. Microsoft's guide to [combining files](https://learn.microsoft.com/power-query/combine-files-overview) describes connecting to a folder, filtering the file list (for example by extension), and choosing Combine. Power Query uses one example file to work out how to read the others. It then creates a set of helper queries, including a Transform Sample File query. Edit that sample query and every file is processed the same way.

The requirement is strict: the files must share the same structure and file type. If someone renames a column in one month's file, expect errors. The combine dialog includes an option to skip files with errors, which keeps the query running but can hide missing data, so check row counts after each refresh.

## A worked example (illustration only)
A retailer with four shops gets a weekly sales CSV from each till system, saved into one shared folder. Each file has a header row, a total row at the bottom, product names with trailing spaces and dates stored as text. The operations lead used to paste the four files together by hand each week.

- Data, Get Data, From File, From Folder. Select the shared folder.
- Filter the Extension column to .csv only, then choose Combine and Transform.
- In the Transform Sample File query, promote headers, filter out the total row, trim the product column and set the date column to Date.
- Back in the main query, set the types for every column, rename the steps clearly and load the result to a table.
- Build the weekly PivotTable or SUMIFS summary on that table.
The following week, the lead drops four new files in the folder, opens the workbook and clicks Refresh All. The steps run on all the files and the summary updates. If one shop's system adds a new column, the lead knows to check the sample query first.

## Common mistakes and when Power Query is the wrong tool
- Relying on automatic type detection, then finding that a column of product codes lost its leading zeros.
- Referring to columns by position or exact header when the source layout is likely to change.
- Leaving step names as defaults, so nobody can follow the logic.
- Pointing a query at a file on one person's desktop, which breaks for everyone else.
- Skipping files with errors and never checking how many rows arrived.
Power Query is the right tool when the same clean-up repeats, when data comes from several files or sources, or when a wide report needs reshaping before analysis. It is overkill for a one-off fix to a few hundred rows, where TRIM, Flash Fill and Remove Duplicates are quicker. It is also not a place to type data in. If people need to enter or edit records, give them a proper input sheet or system, and let Power Query read from it.