Power Query remembers every cleaning step and replays them on next month's file with one click.
Same mess, every month
The bank statement arrives every month with the same three junk rows, the same text amounts and the same trailing spaces. Fixing it by hand takes twenty minutes, every month, with a chance of missing something. Power Query records the twenty minutes once as a list of steps. Next month: replace the file, press Refresh, done.
Get data → Record steps → Load a clean Table → New file arrives → Refresh
| By hand | Power Query | |
|---|---|---|
| First month | 20 minutes | 30 minutes (you're learning) |
| Every month after | 20 minutes, again | Seconds |
| Same steps every time? | Hopefully | Always |
| Can someone else do it? | With instructions | Press Refresh All |
Open a file in Power Query
- Data › From Text/CSV. Choose bank-statement.csv.
- Check the preview. File origin should be 65001: Unicode (UTF-8); Delimiter: Comma.
- Click Transform Data, not Load. Load would put the messy file straight into a sheet.
- The Power Query editor opens. Every change you make from now on is recorded as a step.
| Get data from… | Menu |
|---|---|
| A CSV or text file | Data › From Text/CSV |
| An Excel workbook | Data › Get Data › From File › From Workbook |
| A Table or range in this file | Data › From Table/Range |
| A whole folder of files | Data › Get Data › From File › From Folder (Chapter 7) |
| A web page table | Data › From Web |
The editor in one page
The Power Query editor looks busy, but four areas do most of the work.
| Area | What it's for |
|---|---|
| Ribbon: Home, Transform, Add Column, View | The cleaning commands |
| Data preview | The table as it looks after the selected step; right-click column headers for most commands |
| Formula bar | The selected step written as a formula (View › Formula Bar) |
| Query Settings › Applied Steps | Every step, in order: click one to see the data at that point; × deletes it; ⚙ edits it |
Source
Removed Top Rows
Promoted Headers
Filtered Rows
▸ Changed Type with Locale
Trimmed Text
Added Amount
Clean the bank statement, step by step
- Remove the title rows. Home › Remove Rows › Remove Top Rows: 3.
- Use the first row as headers. Home › Use First Row as Headers.
- Remove the footer rows. Filter Date to drop blanks and rows that start with text (“Opening Balance”, “** Statement…”).
- Set the types with the Indian locale. Next page: dates day first, amounts with lakh commas.
- Tidy the narration. Select it › Transform › Format › Trim, then Clean.
- Add an Amount column. Add Column › Custom Column: deposit minus withdrawal, treating blanks as 0.
- Split the narration (optional). Transform › Split Column › By Delimiter “-”: mode, party, reference.
Custom Column: Amount= (if [Deposit Amt] = null then 0 else [Deposit Amt])
- (if [Withdrawal Amt] = null then 0 else [Withdrawal Amt])Types and locale: dates and lakhs done right
Power Query reads everything in a CSV as text until you set types. The locale tells it how the text is written: which order dates are in, and how numbers are grouped.
- Select the date columns. Date and Value Date.
- Right-click › Change Type › Using Locale. Data type: Date; Locale: English (India).
- Select the amount columns. Withdrawal, Deposit, Closing Balance.
- Change Type › Using Locale. Data type: Decimal Number; Locale: English (India).
Data Type: Date
Locale: English (India)
Sample input values: 31/03/2026
| Text in the file | Without the right locale | With English (India) |
|---|---|---|
| 03/04/2026 | 4 March or 3 April, depending on the computer | 3 April 2026 |
| 1,25,000.00 | An error, or text | 125000 |
| 02/13/2026 (month first) | An error with India locale | Use English (United States) for this source |
What to remember
- Power Query records cleaning steps once and replays them on every refresh.
- Always Transform Data; set types yourself with the right locale; filter by rule, not row number.
- Load to a Table, never type in it, and refresh with Data › Refresh All.
- Query
- A recorded set of steps that produces a clean table.
- Applied Steps
- The list of recorded steps in the editor.
- Locale
- The country format used to read dates and numbers.
This article is adapted from the book. Examples marked “Example” use invented names and numbers; company stories are based on public sources listed in the book. For general learning; use your own judgement for your situation.
