Power Query tutorial: build your first refreshable report

If you rebuild the same report every month — download an export, delete some columns, split a text field, remove blanks, paste into a template — this tutorial replaces that routine with a button.

Power Query records your cleaning steps once and replays them on new data. It is the highest-return feature in Excel for anyone doing recurring reporting work, and most Excel users have never opened it.

The scenario

You receive a monthly sales export. Every month it has the same problems: a few header rows above the real headers, a combined “Region - City” column, amounts stored as text with commas, blank rows scattered through, and a date column in DD-MM-YYYY text format.

Every month you fix them by hand. Today is the last time.

Step 1 — import without opening

Data → Get Data → From File → From Workbook (or From Text/CSV).

Select your file, and in the preview window click Transform Data rather than Load. That opens the Power Query Editor, which is where all the work happens.

Everything you do from here is recorded in the Applied Steps panel on the right. That panel is the whole point — it is your pipeline.

Step 2 — promote the real headers

If the export has junk rows above the headers:

Home → Remove Rows → Remove Top Rows, enter the number of junk rows.

Then Home → Use First Row as Headers.

Two steps appear in Applied Steps. Next month, both replay automatically.

Step 3 — remove blank rows

Home → Remove Rows → Remove Blank Rows.

Also worth doing now: Home → Remove Rows → Remove Duplicates if duplicates are a known problem. Be careful here — only deduplicate when you are certain repeated rows are genuinely errors rather than legitimate repeated transactions.

Step 4 — split the combined column

Select the “Region - City” column.

Home → Split Column → By Delimiter. Choose Custom, enter - (with the spaces), and split at each occurrence.

You get two columns. Rename them by double-clicking the headers.

Step 5 — fix the data types

This is where most manual effort goes, and where Power Query saves the most.

Select the amount column. Click the type icon (ABC or 123) to its left and choose Decimal Number. If it fails because of thousands separators, use Transform → Replace Values to strip the commas first, then set the type.

For the date column, click the type icon and choose Date. If Power Query guesses the format wrongly — likely with DD-MM-YYYY — use Using Locale, choose Date, and set the locale to English (India). This single option resolves most Indian date-format frustration.

Step 6 — trim and clean text

Select your text columns, then Transform → Format → Trim, and again Transform → Format → Clean.

Trim removes leading and trailing spaces. Clean removes non-printing characters. Both are invisible problems that cause lookups to fail and identical-looking values to group separately.

Step 7 — load it back

Home → Close & Load To… and choose Table, or PivotTable Report if the destination is a pivot.

Your cleaned data lands in Excel. More importantly, the query is saved with the workbook.

Step 8 — the payoff

Next month, replace the source file with the new export and click Data → Refresh All.

Every step replays in order. The four-hour routine is now four seconds.

Merging two sources

Once the basic pipeline works, the next most useful thing is combining files.

Merge is Power Query’s join. Home → Merge Queries, pick the matching columns, and choose a join kind — Left Outer keeps all rows from the first query, Inner keeps only matches.

The same rule from SQL applies here: check your row count before and after. If it went up, the key is not unique on one side and you have a fan-out.

Append stacks queries with the same shape on top of each other, which is how you combine twelve monthly files into one table.

Combining a whole folder

The trick worth knowing. Get Data → From File → From Folder, point it at a directory, and Power Query reads every file in it, applies your cleaning steps to each, and stacks the results.

Drop next month’s file into the folder and refresh. Nothing else to do.

Common problems

“The refresh failed because a column is missing.” The source format changed. This is Power Query telling you something upstream broke, which is better than silently producing wrong output.

“My steps applied in the wrong order.” Applied Steps is sequential and you can drag steps to reorder them. Filtering before removing blanks gives a different result from doing it the other way round.

“The file path is hard-coded.” It is, by default. Use Data → Queries & Connections, edit the source step, and point it at a parameter or a folder instead if the file location changes.

What to do next

Take one report you currently rebuild by hand and convert it. Not a practice dataset — a real one, with its real mess.

The first conversion takes an afternoon because you are learning. The second takes twenty minutes. After that you will start noticing manual routines everywhere, which is exactly the habit that makes someone visibly useful in an MIS or accounts role.