Clean a Bank Statement CSV in Excel with Power Query

A bank statement CSV often starts with lines that are not transactions (a title, an account label, a date range), may contain empty rows, and may store every column as plain text. Deleting those by hand works once, but you repeat the work every month.

Power Query records each change as a step in the query. Microsoft describes these as "Applied Steps" in the Query Settings pane, and you can undo a filter by deleting its step. That makes the clean-up inspectable and easy to adjust if next month's file looks slightly different.

Open a query that is already loaded into a worksheet by selecting a cell in the data and choosing Query > Edit; the Power Query Editor opens with that query.

To drop the non-transaction lines at the top of the file, Microsoft documents Home > Remove Rows > Remove Top Rows. A dialog asks for a value in the "Number of rows" field. Count the lines above your column headings and enter that number.

To drop empty rows, use Home > Remove Rows > Remove Blank Rows. If you later find the filter removed something you wanted, delete the corresponding step under Applied Steps.

For CSV and text files, Power Query by default inspects the first 200 rows and adds two steps automatically: "Promote column headers" (the first row becomes the header) and "Changed type" (values move from the Any type to a type based on what each column contains). If your file had extra lines on top, that automatic header step may have promoted the wrong row, so review the steps after removing the top rows.

Microsoft lists four places to change a column type: the Data type dropdown on the Home tab, the Data type dropdown on the Transform tab, the icon at the left of the column heading, and Change Type on the column shortcut menu. Set the date column to Date, amount columns to Decimal number or Fixed decimal number, and identifier columns (check numbers, references) to Text.

Microsoft also recommends that you always explicitly define column data types for queries from unstructured sources such as CSV files, and avoid leaving columns as Any.

Power Query interprets text using a locale. Microsoft gives the example of a locale set to English (United States) with a CSV whose dates are day/month/year: setting the column to Date produces error values, because there is no month 22. The documented fix is to right-click the column heading, choose Change type, then Using locale, and pick the locale that matches the file (English (United Kingdom) in Microsoft's example).

After converting, Microsoft suggests verifying by adding day, month and year columns from the Add column tab and comparing them to the original text.

Illustrative sample (synthetic data) of a file before and after the steps above:

Power Query cleans a CSV that already exists. It cannot recover information that was lost before the CSV was created, and it does not read PDF statements as such. If your statement only exists as a PDF, you first need the transactions as rows.

Your own statements may be laid out differently from the synthetic sample above, so count the lines to remove from your actual file rather than copying a number. If you need the rows extracted from a PDF first, PDFtoRow converts bank statement PDFs to Excel or CSV and shows a free preview of the first 10 rows.