Loading Bank Statement Data into Power BI

Getting converted bank statement data into Power BI, the data model to build, and the measures worth defining.

Power BI can import PDFs directly, but it uses the same table detection as Excel's Power Query and hits the same problems on bank statements. Converting to CSV or Excel first is more reliable.

The data model

A single flat transaction table works, but a small star schema makes the report much easier to build and considerably faster:

Table	Contains	Why separate
Transactions	Date, account, description, debit, credit, balance	The fact table
Date	One row per date, with month, quarter, year, weekday	Enables time intelligence and consistent period filtering
Account	Account number, name, currency, type	Essential for multi-account data
Category	Category, group	Drives the breakdown visuals

Measures worth defining

Total Out — `SUM(Transactions[Debit])`
Total In — `SUM(Transactions[Credit])`
Net — `[Total In] - [Total Out]`
Closing Balance — `CALCULATE(LASTNONBLANK(...))`, which needs care with multiple accounts
Rolling 3-month average — for smoothing spending that varies month to month

Multi-currency is a modelling decision

If your data spans currencies, do not let a measure sum across them. Either filter to one currency in every visual, or add a converted-to-base-currency column at load time using a rate table.

A total that adds dollars and euros is worse than no total, because it looks like a number.

Refreshing as new statements arrive

The practical setup for an ongoing report is a folder of converted files that Power BI reads as a single source. Point the query at the folder rather than at individual files, and each new month's export is picked up on refresh.

That requires every file in the folder to share its columns. Each export keeps its bank's own columns, so statements from one bank in an unchanged layout stack as they are; mixing banks needs a Power Query step that renames each file's columns to one set before they are combined.

Row-level security on shared reports

If a report covers several entities or clients and will be shared, set up row-level security on the account dimension. Bank data is sensitive enough that a filter someone can remove is not adequate protection.

Frequently asked questions

Can Power BI read the PDF directly?

It can try, using the same table detection as Excel. On bank statements it usually finds no tables. Converting first is more reliable.

How do I add each new month automatically?

Point the Power BI query at a folder rather than individual files. Statements from one bank in the same layout share their columns, so each new file is picked up on refresh; for several banks, add a step that renames their columns to one set.

How do I handle several accounts?

An account dimension table joined to the transaction table, with account as a slicer on every page.

Convert a statement now

Bank Statement PDF to Excel, or see every format. More in the guides.