Power Query for Bank Statements: PDF and CSV to Excel

TL;DR: Power Query can read PDFs (in Microsoft 365 Excel for Windows) and CSVs, and it's excellent at combining many monthly files from a folder and cleaning them the same way every time. It struggles with statement layouts where transactions wrap across lines or sit in multiple sections per page. A practical setup: convert PDFs to clean CSVs with a statement converter, drop them in a folder, and let Power Query combine, clean and refresh.

Power Query is the most underused feature in Excel for anyone who handles bank statements. Instead of copying and pasting each month, you build the steps once (import, clean, combine) and click Refresh when new statements arrive. The steps are recorded, repeatable and visible, which is exactly what you want for financial data.

This guide covers both PDF and CSV sources, what works well, where PDF import falls short, and a folder-based setup we use for monthly statements.

What Power Query is, briefly

Power Query is the data import and transformation tool built into Excel (under Data > Get Data). You connect to a source, apply transformation steps in an editor (remove rows, split columns, change types, filter), and load the result to a sheet or the data model. Each step is saved. Refresh re-runs all of them against the current source.

For bank statements, that means:

• Import a file or a whole folder of files.
• Remove headers, footers and junk rows.
• Fix data types (dates, numbers).
• Add columns (signed amount, month, account).
• Combine everything into one table.

Importing a PDF with Power Query

In current Microsoft 365 versions of Excel for Windows, you'll find Data > Get Data > From File > From PDF. (PDF import isn't available in every Excel version or platform, so check yours.)

Steps:

1. Choose the PDF.
2. The Navigator shows the tables and pages Power Query detected. Tables are listed as "Table001" and so on; pages as "Page001."
3. Select a table that looks like transactions and click Transform Data.
4. Clean it in the editor (see below), then Close & Load.

Where PDF import works well

• Digital PDFs (not scans) with a clear, grid-like transaction table.
• Statements where each transaction is one line.
• Consistent layouts across pages.

Where it struggles

• Wrapped descriptions: a transaction whose description spans two lines often becomes two rows, with the amount on one and part of the description on the other.
• Multiple sections: statements that separate deposits, withdrawals, checks and fees into different tables produce several tables per page, sometimes inconsistently detected.
• Check grids: compact multi-column lists of check numbers and amounts come in as wide tables that need reshaping.
• Page-level differences: the first page often has a summary box and a shorter table, so it's detected differently from later pages.
• Scans: Power Query reads the PDF's text layer. A scanned statement without one gives you nothing useful. You'd need OCR first; see our guide to OCR for scanned bank statements.

For a simple statement, Power Query PDF import can be enough. For most real statements, we get better results converting first and using Power Query for what it's best at: combining and cleaning.

The setup we recommend: converter + folder + Power Query

1. Convert each statement to CSV with a statement converter. SheetStatement's bank statement to CSV converter outputs one row per transaction with consistent columns and a balance check, which removes all the PDF layout problems above.
2. Save the CSVs in one folder, one file per statement, consistently named: checking-2026-01.csv, checking-2026-02.csv.
3. Point Power Query at the folder: Data > Get Data > From File > From Folder, then Combine & Transform.
4. Clean once in the editor.
5. Next month, drop the new CSV into the folder and click Refresh.

This gives you the accuracy of a purpose-built converter and the repeatability of Power Query.

Step by step: combining a folder of statement CSVs

1. In Excel, go to Data > Get Data > From File > From Folder.
2. Browse to your folder and click Open.
3. In the dialog, click Combine > Combine & Transform Data.
4. Choose the first file as the sample and confirm the delimiter (comma) and encoding (UTF-8). Power Query creates a helper "Transform Sample File" query and a combined query.
5. In the combined query, you'll see a Source.Name column with each file's name. Keep it. It's your audit trail.
6. Set data types: Date to Date, amounts to Decimal Number. If dates come in wrong, use Change Type > Using Locale and pick the locale that matches the file's format (for example, English (United States) for MM/DD/YYYY).
7. Add a signed amount if needed: Add Column > Custom Column with [Credit] - [Debit] (replace nulls with 0 first using Replace Values).
8. Add a Month column: Add Column > Date > Month > Start of Month, or a custom column Date.ToText([Date], "yyyy-MM").
9. Extract an account name from the file name if you have several accounts: Add Column > Extract > Text Before Delimiter on Source.Name with -.
10. Close & Load to a table.

From now on, adding a file to the folder and clicking Data > Refresh All updates the table.

Cleaning steps worth knowing

Remove top or bottom rows

Home > Remove Rows > Remove Top Rows for title lines; Remove Bottom Rows for totals. If the number varies between files, filter instead: remove rows where Date is null or where Description contains "Total."

Promote headers

Home > Use First Row as Headers. In the folder setup, do this in the sample file query so it applies to every file.

Trim and clean text

Select the description column, then Transform > Format > Trim and Clean. This removes extra spaces and non-printing characters.

Fix amounts with currency symbols

Transform > Replace Values: replace $ with nothing, , with nothing. Then change type to Decimal Number. For parentheses negatives, replace ( with - and ) with nothing.

Merge wrapped descriptions (for PDF imports)

If wrapped descriptions created rows with a description but no date or amount, you can fill down the date and group. A practical approach:

1. Add an index column.
2. Add a custom column that holds the index when Date is not null, and null otherwise.
3. Fill Down that column.
4. Group By it, concatenating descriptions with Text.Combine and taking the first date and amount.

It works, but it's fiddly, and it's the main reason we prefer converting PDFs to CSV first.

Unpivot check grids

If checks come in as a wide table (Number1, Amount1, Number2, Amount2...), select the columns and Transform > Unpivot Columns, then reshape into Number and Amount pairs. Again, possible, but a converter does this for you.

Verifying inside Power Query

Power Query doesn't verify balances for you, but you can make verification easy:

• Keep the file name on every row (Source.Name).
• Load a summary: group by Source.Name and sum the amount. Compare each file's total with its statement's change in balance (closing minus opening).
• Count rows per file and compare with what you expect.

In the worksheet, a small check table with the opening and closing balance for each statement and a formula comparing them with the grouped totals flags any month that's off. Our balance checker is handy for checking individual files before they go into the folder.

Categorizing with Power Query

You can merge a rules table to assign categories in Power Query: load a two-column table (Keyword, Category), then add a custom column that finds the first keyword contained in each description. It's efficient for large datasets. For most people, a worksheet lookup formula is easier to maintain; see Excel formulas for categorizing transactions. A pragmatic split: Power Query for import and cleaning, worksheet formulas for categories, a pivot for reporting.

Several accounts in one model

Once one account works, adding more is straightforward, and this is where Power Query really starts to pay off.

Give each account its own folder, or use one folder with a consistent naming scheme such as checking-2026-03.csv, savings-2026-03.csv, amex-2026-03.csv. Extract the account name from the file name into an Account column, as in step 9 above. Then make sure every file has the same columns. If card exports use different headers from bank exports, rename columns in the sample query so they match before combining.

Two extra steps matter with several accounts:

1. Normalize signs. Decide that money out is negative across all accounts. If card files show purchases as positive, add a step that flips the sign for rows where Account is a card. Keep the original value in another column so each account still verifies against its own statement.
2. Tag transfers. Card payments from checking, and transfers between checking and savings, appear in two files. Add a Transfer flag (by keyword, such as "PAYMENT THANK YOU" or "TRANSFER TO") so your pivot can exclude them.

With that, one refresh gives you a combined view across all accounts with no double counting.

Documenting your query

Power Query records each step with a default name like "Changed Type1" or "Replaced Value3." That's fine for you today, and confusing for whoever opens the workbook next year. Rename steps as you go: "Remove title rows," "Amount = Credit minus Debit," "Flip card signs." Right-click a step and choose Rename, or add a description in Properties.

It takes a minute, and it means a colleague (or an auditor, or you in twelve months) can read the transformation and understand exactly what happened to the data between the bank's file and your report. For financial data, that transparency is a large part of the value.

A worked example

A bookkeeper receives monthly CSVs for a client's checking account from our converter. Each file has Date, Description, Debit, Credit and Balance.

1. She creates the folder Client A / Checking / CSV and saves January to June.
2. From Folder > Combine & Transform with January as the sample.
3. In the sample query: promote headers, set types.
4. In the combined query: replace nulls in Debit and Credit with 0, add Amount = Credit − Debit, add Month, keep Source.Name.
5. Load to a table named Txn.
6. Build a pivot on Txn by Month and Category (category via a lookup formula in an added column on the loaded table).
7. Add a check sheet: one row per month with the statement's opening and closing balance typed in, and a SUMIFS on Txn by Source.Name. All six rows show zero difference.

In July, she saves the new CSV to the folder and refreshes. The table, the pivot and the checks update in seconds. Over a year, that's eleven months of copy-paste avoided, and every month verified the same way.

Power Query vs. other approaches

Approach	Good for	Weak at
Copy and paste	One tiny statement	Accuracy, repeatability
Power Query from PDF	Simple digital statements	Wrapped lines, sections, scans
Statement converter	Accuracy, any layout, scans	Long-term combining (by itself)
Converter + Power Query folder	Monthly repeatable workflows	Initial setup time

For a broader comparison of methods, see PDF to Excel methods compared.

Troubleshooting

Refresh fails after adding a file. The new file has different columns or headers. Check that it was exported in the same format. A converter with fixed output columns avoids this.

Dates are wrong for some files. Different files use different date formats. Standardize at the source, or use Change Type with Locale.

Duplicate rows after refresh. Two files cover overlapping periods, or a file was saved twice with different names. Keep one file per statement period.

Power Query can't see the PDF option. Your Excel version may not include PDF import. Convert to CSV and use the folder method.

Numbers load as text. A currency symbol or a stray space. Replace values, trim, then set the type.

Our take

Power Query is the right tool for the repeatable part of statement work: combining months, cleaning consistently and refreshing. It's the wrong tool for parsing messy PDF layouts. Use a statement converter to get clean CSVs, then let Power Query do what it does best.

Troubleshooting Power Query on bank PDFs

Tables come out split across pages. Power Query often sees each page's table as a separate object. Append them (Home > Append Queries) and then remove repeated header rows with a filter, rather than handling each page manually.

Columns shift on some pages. If a page has a wrapped description or a different layout, columns can slide one place. Check row counts and column types per page before appending. When a statement's pages vary a lot, a dedicated converter may be faster than fixing the query.

Dates or amounts load as text. Set data types explicitly, using Change Type > Using Locale for day-first dates or comma decimals. Avoid relying on automatic type detection, which guesses from the first rows.

Refresh breaks next month. Queries tied to specific table names or column positions break when the bank's layout changes slightly. Filter by column names, and add a step that checks the expected columns exist, so failures are obvious rather than silent.

Scanned PDFs return nothing. Power Query reads text in PDFs; it can't read images. Scanned statements need OCR first. See our guide to OCR for scanned statements.

A second worked example: a check column that catches errors

Add a verification step at the end of your query. In a separate query, group the transactions by statement file and sum the amounts. In a small table next to it, type each statement's opening and closing balances. A formula column then shows Opening + Sum - Closing for each statement. Any non-zero value flags a statement that lost or duplicated rows during import. One reader found a missing page this way in a 24-statement refresh that otherwise looked perfect.

Mini checklist

• Pages appended, repeated headers removed.
• Types set with the right locale.
• Balance-forward and totals rows filtered out.
• Per-statement verification shows zero.
• Query documented for whoever refreshes it next.

FAQ

Can Excel Power Query import a bank statement PDF?

Yes, in Microsoft 365 Excel for Windows using Get Data > From File > From PDF. It works best with simple digital statements; wrapped descriptions, multiple sections and scanned pages cause problems.

How do I combine multiple bank statements in Excel?

Save each statement as a CSV in one folder, then use Data > Get Data > From File > From Folder and Combine & Transform. Keep the file name column so each row traces back to its statement.

Why does Power Query split one transaction into two rows?

Long descriptions that wrap onto a second line in the PDF are read as separate rows. You can merge them with fill-down and grouping steps, or convert the PDF to CSV first.

Does Power Query work with scanned statements?

Not directly. Power Query reads the PDF's text layer, and scans usually don't have one. Run OCR or use a converter that reads page images first.

How do I refresh with a new month's statement?

Add the new CSV to the same folder and click Data > Refresh All. Power Query re-runs all the steps and updates the table.

Can Power Query check that my statement balances?

Not automatically, but you can group by file and sum the amounts, then compare each total with the statement's closing minus opening balance in a small check table.