Power Query: clean & combine files
Power Query (shown as Data → Get & Transform Data in Excel 2016 and later) records your cleaning steps and replays them whenever you click Refresh. If you receive the same file every month, doing the cleanup once in Power Query is the difference between an hour of work and a single click.
The idea
You load data from a source, apply a chain of transformations, and load the result into a sheet or the Data Model. The chain is saved with the workbook as steps in the Applied Steps pane. Behind the scenes Excel writes a script in a language called M, which you can read and edit in the Advanced Editor.
Combining a folder of branch files
Suppose every branch of a Jaipur-based textile business uploads sales_apr.csv, sales_may.csv and so on into
one folder.
- Data → Get Data → From File → From Folder, pick the folder.
- Click Combine → Combine & Transform Data, choose the sample file.
- Power Query builds a sample query, a transform function and an append, all automatically.
- Rename the
Source.Namecolumn toFileand extract the month from it. - Close & Load To… → Only Create Connection plus Add this data to the Data Model if you will use pivots.
Next month you drop a new file in the folder, press Data → Refresh All, and the report grows by itself.
Transformations worth knowing
| Goal | Step |
|---|---|
| First row holds headers | Home → Use First Row as Headers |
| Column is a text date | Transform → Data Type → Using Locale → Date, English (India) |
| Blank/junk rows at the top | Home → Remove Rows → Remove Top Rows |
| Month columns Apr, May, Jun | Select them → Transform → Unpivot Columns |
| Add customer master details | Home → Merge Queries (Left Outer) on Customer Code |
| Stack two similar tables | Home → Append Queries |
| Totals by state | Transform → Group By, Sum of Taxable |
| Computed column | Add Column → Custom Column |
A custom column for GST reads like a formula, but the syntax is M, not Excel:
= Table.AddColumn(#"Changed Type", "GST",
each Number.Round([Taxable] * [Rate] / 100, 2), type number)
Note that M is case-sensitive: Table.AddColumn works, table.addcolumn does not.
Why locale matters in India
A column of 07/04/2026 read on a machine set to US English becomes 7 April only by luck, and 13/04/2026 errors
out entirely. Always use Change Type With Locale → Date → English (India) so dd/mm/yyyy is interpreted
correctly. This single step prevents the most common Power Query bug in Indian offices.
Keep queries maintainable
- Rename each step in Applied Steps to what it does ("Remove blank invoices" beats "Filtered Rows1").
- Put the folder path in a cell, load it as a parameter, and stop hard-coding a path that points at your own desktop.
- Filter and remove unneeded columns early, so later steps work on less data.
- Load large tables to Connection Only + Data Model, not to a sheet, to stay under the one-million-row limit.
Common mistakes
- Clicking Combine before checking that every file has the same column names; one renamed header produces a column of nulls.
- Leaving an auto-generated
Changed Typestep as the second step, which breaks when a new file has an extra column. - Removing columns by position instead of by name.
- Refreshing a query whose source file is open in another window, which locks it.
Practice
Create three CSV files of branch sales for April, May and June with identical headers. Use Get Data → From Folder to combine them, extract the month from the file name, fix the date column using the English (India) locale, add a GST column, group by state and month, and load the result to a sheet. Then add a fourth file and confirm that Refresh All picks it up.