How to Clean a Messy Bank Statement in Power Query

You open the bank statement export, and it is a mess. A few junk rows at the top, dates that Excel refuses to treat as dates, descriptions padded with double spaces, and a totals row sitting at the bottom. In this article I will show you how I tidy all of that in Power Query once, so that next month you just press refresh and get on with the reconciliation.

What does “clean” actually mean for a bank statement?

Before you click anything, know the target. A reconciliation needs:

• One row per real transaction. No opening balance row, no totals row, no blank rows.
• A real date type, read as day first in the Australian format.
• Amounts as numbers, not text. Either one signed amount, or tidy separate Debit and Credit columns.
• Clean description text, with the extra spaces removed.

That is the whole goal. Everything below is just getting there.

Power Query or VBA for this job?

For a bank import you repeat every month, Power Query wins. You set the steps up once, and next month you point it at the new file and press refresh. There is no code to maintain and nothing to enable. Reach for VBA when you need looping logic or a once off fix that Power Query cannot express. A recurring bank statement is the classic Power Query job.

The messy file we start with

Here is a typical export from an Australian small business account. The real transactions sit under five preamble rows (account name, BSB and account number, statement period, and a blank row).

Date	Description	Debit	Credit	Balance
01/05/2026	Opening Balance			4825.50
02/05/2026	EFTPOS SQ COASTLINE CAFE		1284.00	6109.50
03/05/2026	DIRECT DEBIT XERO SUBSCRIPTION	69.00		6040.50
05/05/2026	TRANSFER TO SUPPLIER BIDFOOD AUST	2310.75		3729.75
05/05/2026	TRANSFER TO SUPPLIER BIDFOOD AUST	2310.75		3729.75
15/05/2026	BANK FEE MERCHANT SERVICE FEE	42.90		7647.05
	Totals	4733.40	5245.38

Look at everything wrong here. The dates are text in day first format. The descriptions have double spaces. There is an Opening Balance row that is not a transaction. There is a Totals row at the bottom. There is a running Balance column the reconciliation does not need. And there are two identical Bidfood lines on the fifth of May, which may be a real second payment or a double posting.

Cleaning it step by step

Figure · Power Query

From messy export to a table you can reconcile

1. Messy export

   • junk header rows
   • dates as text
   • debits and credits tagged DR / CR
   • running balance + totals row

2. Power Query

   • remove top rows
   • promote headers
   • trim and clean text
   • split DR / CR to numbers
   • set types using the AU locale
   • filter out non transactions

3. Clean table

   • one row per transaction
   • real date type
   • numeric Debit / Credit
   • no junk, ready to reconcile

1. Get the data into Power Query. On the Data tab choose Get Data, From File, pick the export, then choose Transform Data so it opens in the editor instead of loading straight to a sheet.

2. Remove the junk rows at the top. Home, Remove Rows, Remove Top Rows, then enter the number of preamble lines. Now the real header row sits at the very top.

3. Promote the real headers. Home, Use First Row as Headers. Date, Description, Debit, Credit and Balance become proper column names instead of data.

4. Read the Balance, then remove it. The reconciliation does not need the running balance, but glance at the opening and closing figures first so you can prove your result at the end. Then right click the Balance column and choose Remove.

5. Trim and clean the description. Select Description, then Transform, Format, Trim, and again Transform, Format, Clean. Trim removes the spaces at each end, and Clean removes hidden characters. For the stubborn double spaces inside the text, add one small step:

// Replace any double space inside the description with a single space
Table.ReplaceValue(Source, "  ", " ", Replacer.ReplaceText, {"Description"})

6. Set the data types yourself, using the locale. Click the type icon on Date, choose Using Locale, then Date with English (Australia), so the day first dates parse correctly. Set Debit and Credit to Fixed Decimal Number, which is the currency type, not the floating type. Setting types by hand, rather than by automatic detection, is what makes the query survive next month.

7. Remove the rows that are not transactions. Open the filter menu on Date and remove errors and blanks. That clears the Totals row, which has no date. Then filter out the rows where both Debit and Credit are empty, which removes the Opening Balance row. This is the step beginners skip, and it is the one that makes your totals tie out.

8. Name the query and load it. Rename it to something like BankStatement_Clean, then Close and Load To, and choose a table or a connection only, depending on what feeds your reconciliation.

Following one row all the way through

Take this raw line:

02/05/2026 | EFTPOS SQ COASTLINE CAFE | (blank) | 1284.00 | 6109.50

After the steps above it becomes one clean record: the date is a real date, the description reads “EFTPOS SQ COASTLINE CAFE” with single spaces, the Credit is the number 1284.00, and the running balance is gone. That is exactly the shape a reconciliation can use.

Wrapping up

Clean the file once, and next month is a single refresh. Promote, prune, trim, set the types with the right locale, filter out the junk, and prove it against the closing balance. A clean bank table lets you line the bank’s record up against your own books, tick off what matches, and quickly see what does not, which is exactly what a bank reconciliation is.

A nice bonus: the same Power Query steps run inside Power BI too, so this skill carries straight into a refreshable dashboard later.

Grab the sample workbook and try it yourself.