Level 1 · Import a CSV and clean it in three steps

Every analyst has a file that arrives each month and needs the same clean-up each time. In Excel, Power Query is the tool for that: you connect to the file, and every change you make is recorded as a step. Next month, the steps replay on the new file by themselves.

What you are working in

This page is a practice editor for Power Query’s way of working, not Power Query itself. The table at the top is the Source step: the CSV as it was imported. You add steps in the box, one per line, and the editor shows the table after each step, like the Applied Steps pane in Excel. The step names and results match Power Query’s, so what you practise here is what you click at your desk.

In real Excel the same import goes like this:

1. Data → Get Data → From File → From Text/CSV (or Data → From Text/CSV), pick marketplace_june_kitchen.csv.
2. A preview opens. Excel guesses the delimiter (comma, semicolon, tab) and the column types. Click Transform Data instead of Load, so the Power Query Editor opens.
3. The Applied Steps pane on the right already shows Source, Promoted Headers (the first row became the column names) and usually Changed Type (Excel’s guess at the types).

The original CSV is never touched. Power Query reads it and builds a cleaned copy; that is why you can always undo a step by deleting it from the list.

Three steps on this file

The source has 32 rows: order lines for Kitchen products sold on the marketplace in June 2025.

Step	What it does here	In Excel
Remove columns: customer_code	drops the column the buyer doesn’t need	right-click the header → Remove
Filter rows: status <> cancelled	keeps 27 rows; the 5 lines of 4 cancelled orders go	arrow on the status header → untick cancelled
Change type: unit_price to decimal	"16.99" (text) becomes 16.99 (a number)	click the ABC icon on the header → Decimal Number

After the filter, status only holds completed. Order 9888 is a good one to watch: it had two lines and both disappear, because the filter tests each row on its own.

Why the price needs a type

unit_price came in as text. You can see it in the source: the marketplace writes 109.00, with the trailing zeros a number would drop. In Excel’s editor the column header shows ABC for text, 1.2 for a decimal and 123 for a whole number.

Excel’s automatic Changed Type step is a guess based on the first rows, and exports with quoted or oddly formatted numbers often come in as text. Text looks like a number until you try to add it up: SUM skips it, and a multiplication in a custom column fails. Set the type yourself on every column you will calculate with. Here quantity is already a whole number, so only the price needs the step.

The M behind the steps

Each click writes one line of M, Power Query’s formula language. You never have to type it, but it is good to recognise it in View → Advanced Editor:

#"Removed Columns" = Table.RemoveColumns(#"Promoted Headers", {"customer_code"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([status] <> "cancelled")),
#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows", {{"unit_price", type number}})

Each step names the step before it. That chain is the Applied Steps list.

Load and refresh

When the table looks right, Home → Close & Load puts it on a new sheet as an Excel Table. Next month, save the July file over the June one (same name, same folder) and press Data → Refresh All: the three steps run again on the new rows. If the file moves, edit the Source step (the gear icon next to it) and point it at the new path.

Common mistakes

• Writing the value in a different case. Power Query compares text exactly: status <> Cancelled keeps every row, because no row says Cancelled with a capital C.
• Skipping Change type. The prices look like numbers but stay text, and every total built on them breaks.
• Removing a column a later step needs. Steps run in order. If a filter on customer_code came after Remove columns: customer_code, it would fail with “There is no column”.
• Clicking Load instead of Transform Data. The raw file lands on a sheet as-is. Open the query again with Data → Queries & Connections and double-click it.