4.1Power Query: import & transform data
The essentials in 30 seconds
- Power Query = the replayable cleaning workshop: you import (file, folder, database), you transform through recorded steps (filter, type, split, unpivot) and you load a clean table — the ETL flow replays identically.
- Refresh changes everything: the next month, Wassim replaces the source file and clicks Refresh — all the steps replay by themselves, no more manual cleaning on Monday morning.
- Wassim's reflex: consolidate by folder (Combine) for recurring monthly files, and type the columns early to avoid downstream errors.
Running mini-case — Wassim consolidates 12 sales files without spending his evenings on it
Wassim, an accountant in Algiers, receives a sales file in the same format every month: he used to spend hours cleaning it by hand (useless columns, dates as text, empty rows). He builds a Power Query flow From a Folder that combines the 12 files, removes the useless columns, types the amounts and filters the empty rows. In January, he drops in the 13th file and clicks Refresh: everything is included. First milestone of the running thread: the cleaning is built once, then replayed indefinitely.
This same case returns in the course, the diagram, the script and the 48h challenge — and continues in 4.2, 4.3, 4.4 and 4.5.
1. The replayable cleaning workshop
Power Query (Data ▸ Get Data) imports data (file, folder, web, database) and transforms it: remove columns, filter, replace, split, change types, unpivot… Each action is recorded as an applied step, visible and reorderable in the right pane. The result: a replayable ETL (Extract-Transform-Load) process — Wassim re-reads his steps like a recipe.
The golden rule: clean in Power Query, never by hand in the sheet. What is done in the sheet does not replay; what is done in steps does replay. And you type the columns early (date, number, text): an amount left as text blocks all downstream calculations.
2. The power of refresh
The big value: the next month, you replace the source file and click Refresh — all the steps replay automatically on the new data. It's the cure for the « Monday morning workbooks » that Wassim used to reprocess manually for hours at each closing.
Watch the fragile point: if you rename or move the source, the flow can no longer find it. So you fix a stable location (fixed folder, constant naming) and check the source at the top of the steps before refreshing.
3. Consolidate several files
Power Query excels at merging (joins between tables on a key, like an XLOOKUP at scale) and at appending (stacking several files from a folder into a single table). Ideal to consolidate Wassim's 12 monthly files into a single report, updated in one click.
The language under the hood is called M: each step generates a line of it, readable in the advanced editor. You don't need to write it by hand to start — but knowing how to re-read it lets you diagnose a step that breaks (renamed column, inconsistent type).
🔎 Concrete example — 🔎 Concrete example — Consolidate 12 monthly files
Wassim's « Sales 2026 » folder contains 12 files in the same format:
Power Query ▸ From a Folder ▸ Combine
→ steps: remove useless columns, set types, filter empty rows
→ Load: a single table of the whole year's sales
Next January: drop in the 13th file → Refresh → included
// M extract (advanced editor) — equivalent steps:
let
Source = Folder.Files("C:\Compta\Ventes 2026"),
FilterXlsx = Table.SelectRows(Source, each [Extension] = ".xlsx"),
CleanColumns = Table.SelectColumns(FilterXlsx, {"Name", "Content"})
in
CleanColumnsReading: the transformation steps apply to all the files in the folder, including future ones.
Lesson: you build the process once, then refresh it indefinitely.
Section vocabulary
- Power Query
- A data import and transformation tool using recorded, replayable steps.
Simple example: Wassim combines his 12 monthly files there into a single, refreshable table.
What it is not: a simple manual copy-paste to redo every month.
Reflex: recurring monthly cleaning: think Power Query, not sheet.
- ETL
- Extract, Transform, Load: the data preparation flow.
Simple example: Wassim extracts from the folder, transforms in steps, loads a clean table.
What it is not: an isolated formula in a cell.
Reflex: preparing raw data: think in the 3 ETL stages.
- Applied step
- Each recorded transformation, replayed automatically on refresh.
Simple example: Wassim's amount typing replays on each new file.
What it is not: a manual tweak in the sheet, lost the next month.
Reflex: every cleaning: a named, checkable step.
- Merge / Append
- Join two tables on a key (merge) or stack several tables (append).
Simple example: Wassim appends the 12 months into one table; he will merge customers and sales on the customer code.
What it is not: an XLOOKUP copied down thousands of rows.
Reflex: consolidate: append to stack, merge to join.
In practice — In practice — A first Power Query flow
- Import a file (CSV/Excel) via Data ▸ Get Data.
- Clean: remove columns, filter rows, fix the types.
- Load the result into an Excel table.
- Change the source then Refresh: check that the steps replay.
Ready-to-say script — pitch Power Query to your boss (30 s)
« This is Wassim: every month I used to clean the sales file by hand for hours. I built the cleaning once in Power Query — recorded steps, a single table. Now I drop in the new file and click Refresh: everything replays. The source is never modified, and every step can be checked. »
Say it when you're asked to « quickly redo this month's file »: show the Refresh in front of them.
Copy-paste template — M skeleton: filter and type (advanced editor)
let
Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
TypesOkay = Table.TransformColumnTypes(Source, {{"Date", type date}, {"Amount", type number}}),
NoBlanks = Table.SelectRows(TypesOkay, each [Amount] <> null and [Customer] <> null),
UsefulColumns = Table.RemoveColumns(NoBlanks, {"Comment"})
in
UsefulColumnsRecurring monthly files
Like Wassim: From a Folder ▸ Combine, then Refresh each month on the new file dropped in.
Web data (rates, reference lists)
Import from the web then Refresh before each closing: the reference list updates without retyping.
Customer-sales join
Merge Sales and Customers on the customer code instead of multiplying XLOOKUPs over thousands of rows.
Source moved or renamed
Change the first step (path) then Refresh: all the following steps replay on the right source.
✓ Best practices
- Clean in Power Query, not by hand in the sheet.
- Set column types early in the steps.
- Consolidate by folder for recurring files.
✗ Common pitfalls
- Redoing the cleaning manually every month.
- Loading without typing → downstream errors.
- Renaming/moving the source → the flow can no longer find it.
Key takeaways
- Power Query imports and transforms by replayable steps (ETL).
- Refresh replays all the cleaning on new data.
- Merge (join) and Append (stack) consolidate tables.
- You build the process once, then refresh it.
48h challenge — apply tomorrow
On a real recurring file: import it into Power Query, remove a column, type the amounts, filter the blanks, load, then Refresh after changing the source.
- I know how to import and transform through recorded steps.
- I know how to consolidate by folder (Combine) and re-read a step.
- I know how to refresh on new data without redoing everything.
Frequently asked questions
Does Power Query modify my source data?
No: it reads the source and produces a transformed copy in Excel. The original source stays intact; that's why the process is safe and replayable.
Difference between Power Query and a macro?
Power Query is designed for data preparation (no code, by steps); VBA is a general-purpose programming language. To clean/consolidate, Power Query is simpler, more robust and refreshable.
What is the major advantage of Power Query for a monthly report?