Your first Power Query pipeline: from monthly clean-up to one click

Ask people what takes the longest in their reporting work and the answer is rarely "the analysis". It is the preparation: opening the export, deleting the four header rows, splitting the reference column, fixing the dates that arrived as text, removing duplicates, and pasting the result into the template. Every month, the same sequence, done by hand.

Power Query exists to record that sequence once and replay it on demand. It ships inside Excel — 2016 and later on Windows, and in Microsoft 365 on Mac — under Data → Get Data.

The mental model

A query is an ordered list of steps applied to a source. You do not edit data; you edit the recipe. When next month's file arrives, the recipe runs again from the top. That distinction is the whole idea, and it is why a query survives a new file while a manual clean-up does not.

A worked example

Say you receive a monthly sales export with a title block above the real headers, a customer reference like FR-10432-A that needs splitting, dates as text, and a stray total row.

1. Connect. Data → Get Data → From File → From Workbook. Select the file, choose the sheet, and click Transform Data rather than Load — you want the editor, not the raw import.
2. Remove the title block. Home → Remove Rows → Remove Top Rows → 4.
3. Promote headers. Home → Use First Row as Headers.
4. Split the reference. Select the column, Transform → Split Column → By Delimiter → hyphen.
5. Fix the dates. Click the type icon on the date column and set Date. If it fails, set Locale explicitly — this is where most date problems come from, not from the data itself.
6. Remove the total row. Filter out the row where the customer field is blank or reads "Total". Filter on a condition, never on a row number.
7. Load. Home → Close & Load To → Table, or straight to the data model if you plan to build a PivotTable on top.

Next month: replace the source file, right-click the table, Refresh. That is the entire process.

The three mistakes that make queries fragile

Removing rows by position instead of by condition. "Remove top 4 rows" is fine for a fixed export template. "Remove rows 15 to 18" is a time bomb, because next month the junk will be somewhere else. Filter on what the row is, not where it sits.

Hard-coding the file path. The query stores an absolute path, so the file works on your machine and fails on your colleague's. Create a parameter for the folder, or use a path relative to the workbook, and the query moves with the file.

Renaming columns early. If you rename a column in step 2 and the source later renames it upstream, every subsequent step breaks. Do renaming near the end, once the shape is stable.

The step that changes everything: folder combine

Once one file works, point the query at a folder instead: Get Data → From File → From Folder. Power Query reads every file in it and stacks them into one table. Dropping next month's export into the folder and hitting refresh is the whole monthly process. This is where the hours actually come back.

How long does it take to learn?

The basics — connect, shape, load, refresh — take an afternoon. Unpivoting, merging and folder combine take a few hours more. Against several days a year spent on manual clean-up, it is the clearest return on time available anywhere in Excel.

More articles