Turn a bank statement CSV into ledger-ready rows with Power Query
Use case · By Tahir Nas · Examples run in our simulator · Updated
14 min read
To turn a bank statement CSV into ledger-ready rows with Power Query, skip the banner lines above the header, promote the real header row, convert dates and amounts with the bank's locale, and combine the Paid out and Paid in columns into one signed amount. Then map each line to a nominal account (the ledger account it is posted to, such as Office costs) with a keyword table, and check the running balance line by line against the bank's figures before anything reaches the ledger.
What a typical bank export looks like
Most business bank accounts let you download transactions as CSV. The layouts differ between banks, but the problems repeat: a few lines of account details above the header, dates in the bank's local format, amounts with thousands separators wrapped in quotes, separate columns for money out and money in, a running balance, and sometimes an opening balance row.
The example below is a short statement written as text, so it runs anywhere. Csv.Document accepts text as well as a file's contents, and its QuoteStyle.Csv option treats line breaks inside quoted values as data rather than the end of a row (Csv.Document, Microsoft Learn). The post on CSV quirks with line breaks, quotes and delimiters covers the other parser options.
let
Raw = "Account: 12345678,,,,#(lf)Statement period: 01/10/2026 to 31/10/2026,,,,#(lf)Date,Description,Paid out,Paid in,Balance#(lf)01/10/2026,Opening balance,,,""2,500.00""#(lf)02/10/2026,CARD PAYMENT OFFICE SUPPLIES LTD,45.60,,""2,454.40""#(lf)03/10/2026,BACS ALDER LTD INV-087,,450.00,""2,904.40""#(lf)06/10/2026,DIRECT DEBIT CITY ENERGY,""1,210.00"",,""1,694.40""#(lf)07/10/2026,BANK CHARGES,8.50,,""1,685.90""",
Csv = Csv.Document(Raw, [Delimiter = ",", Columns = 5, QuoteStyle = QuoteStyle.Csv])
in
CsvResult
| Column1 | Column2 | Column3 | Column4 | Column5 |
|---|---|---|---|---|
| Account: 12345678 | ||||
| Statement period: 01/10/2026 to 31/10/2026 | ||||
| Date | Description | Paid out | Paid in | Balance |
| 01/10/2026 | Opening balance | 2,500.00 | ||
| 02/10/2026 | CARD PAYMENT OFFICE SUPPLIES LTD | 45.60 | 2,454.40 | |
| 03/10/2026 | BACS ALDER LTD INV-087 | 450.00 | 2,904.40 | |
| 06/10/2026 | DIRECT DEBIT CITY ENERGY | 1,210.00 | 1,694.40 | |
| 07/10/2026 | BANK CHARGES | 8.50 | 1,685.90 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The result has eight rows of text in Column1 to Column5. The first two rows are the account banner, the third holds the real headers, and the fourth is the opening balance. Quoted amounts such as "2,500.00" arrive as a single value with the comma kept, which is why the bank's quotes matter: without the quotes around the amount, its comma would split it into two columns. The lesson on importing CSV files covers delimiters and encodings in more detail.
Step 1: remove the banner and set types with the bank's locale
Skip the two banner rows, promote the headers, set aside the opening balance line (Step 3 uses it), and set the types. The third argument of Table.TransformColumnTypes is the culture used to read the text (Table.TransformColumnTypes, Microsoft Learn). For a UK bank that is "en-GB", which reads 03/10/2026 as 3 October and "1,210.00" as one thousand two hundred and ten.
let
Raw = "Account: 12345678,,,,#(lf)Statement period: 01/10/2026 to 31/10/2026,,,,#(lf)Date,Description,Paid out,Paid in,Balance#(lf)01/10/2026,Opening balance,,,""2,500.00""#(lf)02/10/2026,CARD PAYMENT OFFICE SUPPLIES LTD,45.60,,""2,454.40""#(lf)03/10/2026,BACS ALDER LTD INV-087,,450.00,""2,904.40""#(lf)06/10/2026,DIRECT DEBIT CITY ENERGY,""1,210.00"",,""1,694.40""#(lf)07/10/2026,BANK CHARGES,8.50,,""1,685.90""",
Csv = Csv.Document(Raw, [Delimiter = ",", Columns = 5, QuoteStyle = QuoteStyle.Csv]),
Skipped = Table.Skip(Csv, 2),
Promoted = Table.PromoteHeaders(Skipped, [PromoteAllScalars = true]),
NoOpening = Table.SelectRows(Promoted, each [Description] <> "Opening balance"),
Typed = Table.TransformColumnTypes(NoOpening, {
{"Date", type date}, {"Paid out", type number}, {"Paid in", type number}, {"Balance", type number}
}, "en-GB")
in
TypedResult
| Date | Description | Paid out | Paid in | Balance |
|---|---|---|---|---|
| 2026-10-02 | CARD PAYMENT OFFICE SUPPLIES LTD | 45.6 | null | 2454.4 |
| 2026-10-03 | BACS ALDER LTD INV-087 | null | 450 | 2904.4 |
| 2026-10-06 | DIRECT DEBIT CITY ENERGY | 1210 | null | 1694.4 |
| 2026-10-07 | BANK CHARGES | 8.5 | null | 1685.9 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Four transactions remain, dated 2 to 7 October, with empty amount cells turned into null and "1,210.00" read as 1210. Microsoft's data types article describes this exact trap: with the locale set to English (United States), a column of UK day/month/year dates produces errors, and Change type using locale with English (United Kingdom) fixes it (Data types in Power Query, Microsoft Learn). The guide to changing type using locale shows the same fix through the ribbon.
When the banner isn't always two rows
Some banks add an extra line in some months, such as a notice about a product change. A fixed Table.Skip(Csv, 2) then promotes the wrong row. Table.Skip also accepts a condition, so Table.Skip(Csv, each [Column1] <> "Date") skips rows until it reaches the one that starts with "Date". The lesson on removing report banners before promoting headers walks through both forms.
Step 2: one signed amount and a nominal account
From the business's point of view, money in increases the bank balance and money out reduces it. A single signed Amount column (positive in, negative out) is simpler to post and to total than two columns that are each half empty. The nominal account comes from a small rules table: each keyword that appears in the description, in the right direction, maps to an account. Lines with no match go to suspense so someone reviews them.
let
Statement = #table(
type table [Date = date, Description = text, PaidOut = nullable number, PaidIn = nullable number, Balance = number],
{
{#date(2026, 10, 2), "CARD PAYMENT OFFICE SUPPLIES LTD", 45.60, null, 2454.40},
{#date(2026, 10, 3), "BACS ALDER LTD INV-087", null, 450.00, 2904.40},
{#date(2026, 10, 6), "DIRECT DEBIT CITY ENERGY", 1210.00, null, 1694.40},
{#date(2026, 10, 7), "BANK CHARGES", 8.50, null, 1685.90}
}
),
Rules = #table(
type table [Keyword = text, Direction = text, Account = text],
{
{"OFFICE SUPPLIES", "out", "6200 Office costs"},
{"ENERGY", "out", "6400 Utilities"},
{"BANK CHARGES", "out", "6900 Bank charges"},
{"BACS", "in", "1100 Trade debtors"}
}
),
Amount = Table.AddColumn(Statement, "Amount", each
(if [PaidIn] = null then 0 else [PaidIn]) - (if [PaidOut] = null then 0 else [PaidOut]), type number),
Account = Table.AddColumn(Amount, "Account", (row) =>
let
dir = if row[Amount] >= 0 then "in" else "out",
hits = Table.SelectRows(Rules, each Text.Contains(row[Description], [Keyword]) and ([Direction] = dir or [Direction] = "any"))[Account]
in
if List.IsEmpty(hits) then "9999 Suspense" else List.First(hits), type text),
Ledger = Table.SelectColumns(Account, {"Date", "Description", "Account", "Amount"})
in
LedgerResult
| Date | Description | Account | Amount |
|---|---|---|---|
| 2026-10-02 | CARD PAYMENT OFFICE SUPPLIES LTD | 6200 Office costs | -45.6 |
| 2026-10-03 | BACS ALDER LTD INV-087 | 1100 Trade debtors | 450 |
| 2026-10-06 | DIRECT DEBIT CITY ENERGY | 6400 Utilities | -1210 |
| 2026-10-07 | BANK CHARGES | 6900 Bank charges | -8.5 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The result is four ledger-ready rows:
| Date | Description | Account | Amount |
|---|---|---|---|
| 2026-10-02 | CARD PAYMENT OFFICE SUPPLIES LTD | 6200 Office costs | -45.6 |
| 2026-10-03 | BACS ALDER LTD INV-087 | 1100 Trade debtors | 450 |
| 2026-10-06 | DIRECT DEBIT CITY ENERGY | 6400 Utilities | -1210 |
| 2026-10-07 | BANK CHARGES | 6900 Bank charges | -8.5 |
The columns are named PaidOut and PaidIn here to keep the code short; the combined query at the end uses the bank's own names.
A few points about the rules table:
- Order matters.
List.Firsttakes the first matching rule, so put specific keywords above general ones ("BACS ALDER" above "BACS"). - Direction matters. BACS is an electronic bank transfer used both to receive and to make payments, so the "BACS" rule only applies to money in. A supplier payment by BACS goes to suspense until it has its own rule. Set Direction to "any" for a rule that applies both ways.
Text.Containsis case-sensitive by default. Bank descriptions are usually upper case, so keep keywords in upper case or passComparer.OrdinalIgnoreCaseas the third argument.- Keep the rules in an Excel table that the finance team edits. The query doesn't change when a new supplier appears; only the table does.
The mapping tables vs nested if guide explains why a rules table is easier to maintain than a long chain of conditions.
Step 3: prove the statement balances
A bank statement carries its own checks. Each row's balance should equal the previous balance plus that row's amount, starting from the opening balance, and the last movement should land on the closing balance printed on the statement. If a filter drops a line or a stray delimiter splits one, these checks fail. Make the query fail too, with a message that says where, rather than load a ledger that is quietly out.
let
Opening = 2500.00,
PrintedClosing = 1685.90,
Statement = #table(
type table [Date = date, Amount = number, Balance = number],
{
{#date(2026, 10, 2), -45.60, 2454.40},
{#date(2026, 10, 3), 450.00, 2904.40},
{#date(2026, 10, 6), -1210.00, 1694.40},
{#date(2026, 10, 7), -8.50, 1685.90}
}
),
Indexed = Table.AddIndexColumn(Statement, "Line", 0, 1),
Previous = {Opening} & Indexed[Balance],
Checked = Table.AddColumn(Indexed, "BalanceOK", each Number.Round(Previous{[Line]} + [Amount] - [Balance], 2) = 0, type logical),
BadLines = Table.SelectRows(Checked, each not [BalanceOK])[Line],
Movement = Number.Round(Opening + List.Sum(Statement[Amount]) - PrintedClosing, 2),
Result =
if not List.IsEmpty(BadLines) then error "Running balance breaks at line " & Text.From(BadLines{0} + 1)
else if Movement <> 0 then error "Movements don't reach the printed closing balance; difference " & Number.ToText(Movement)
else Statement
in
ResultResult
| Date | Amount | Balance |
|---|---|---|
| 2026-10-02 | -45.6 | 2454.4 |
| 2026-10-03 | 450 | 2904.4 |
| 2026-10-06 | -1210 | 1694.4 |
| 2026-10-07 | -8.5 | 1685.9 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Every row agrees with the one before it, and the movements total -814.10, which takes 2,500.00 to the printed 1,685.90, so the query returns the four rows. Previous is the opening balance followed by each row's balance, so Previous{[Line]} is the balance just before that row. The differences are rounded to two decimal places because decimal numbers in M are binary floating-point values that can't represent every amount exactly (Data types in Power Query, Microsoft Learn).
The same check with the BACS receipt missing:
let
Opening = 2500.00,
PrintedClosing = 1685.90,
Statement = #table(
type table [Date = date, Amount = number, Balance = number],
{
{#date(2026, 10, 2), -45.60, 2454.40},
{#date(2026, 10, 6), -1210.00, 1694.40},
{#date(2026, 10, 7), -8.50, 1685.90}
}
),
Indexed = Table.AddIndexColumn(Statement, "Line", 0, 1),
Previous = {Opening} & Indexed[Balance],
Checked = Table.AddColumn(Indexed, "BalanceOK", each Number.Round(Previous{[Line]} + [Amount] - [Balance], 2) = 0, type logical),
BadLines = Table.SelectRows(Checked, each not [BalanceOK])[Line],
Movement = Number.Round(Opening + List.Sum(Statement[Amount]) - PrintedClosing, 2),
Result =
if not List.IsEmpty(BadLines) then error "Running balance breaks at line " & Text.From(BadLines{0} + 1)
else if Movement <> 0 then error "Movements don't reach the printed closing balance; difference " & Number.ToText(Movement)
else Statement
in
ResultResult: an error
Expression.Error: Running balance breaks at line 2
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The simulator raises Running balance breaks at line 2: 2,454.40 less 1,210.00 is 1,244.40, not the 1,694.40 on that row.
The row check alone can't see a line missing from the end, because the last remaining row still agrees with the one before it. That is the job of the printed closing balance: with the bank charge removed instead, the same query raises Movements don't reach the printed closing balance; difference 8.5. Take PrintedClosing from the statement or online banking, never from the Balance column you are checking.
The row check also assumes the rows run oldest first. Many banks export newest first; reverse the table with Table.ReverseRows before adding the index, rather than sorting by date, because several lines on the same day have no reliable order of their own. The lesson on validating data and failing loudly covers this pattern for other checks.
Formats from other banks
A single amount column with DR and CR
Some banks use one Amount column with a "DR" or "CR" suffix, or show money out in brackets. Parse the text once, decide the sign, then convert the digits with the bank's culture.
let
Raw = #table({"Date", "Description", "Amount"}, {
{"02/10/2026", "CARD PAYMENT", "45.60 DR"},
{"03/10/2026", "BACS ALDER LTD", "450.00 CR"},
{"09/10/2026", "STANDING ORDER", "(12.00)"}
}),
Signed = Table.TransformColumns(Raw, {{"Amount", each
let
t = Text.Trim(_),
neg = Text.EndsWith(t, "DR") or Text.StartsWith(t, "("),
digits = Text.Remove(t, {"D", "R", "C", "(", ")", " ", ","}),
n = Number.FromText(digits, "en-GB")
in
if neg then -n else n, type number}})
in
SignedResult
| Date | Description | Amount |
|---|---|---|
| 02/10/2026 | CARD PAYMENT | -45.6 |
| 03/10/2026 | BACS ALDER LTD | 450 |
| 09/10/2026 | STANDING ORDER | -12 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The three amounts become -45.6, 450 and -12. On a bank statement, DR means money out of the account and CR money in, which is the opposite of how the same movement is posted to the bank account in your own ledger. Check one known payment against your online banking before trusting the rule. The text functions cheat sheet lists the other text helpers used here.
Decimal commas
A bank in Germany writes one thousand two hundred and ten as "1.210,00". Read it with that country's culture rather than replacing characters by hand: Number.FromText("1.210,00", "de-DE") returns 1210 in the simulator. The exercise on reading amounts with a decimal comma practises this.
Reading the real file
In Excel or Power BI, the first step reads the downloaded file instead of a text literal. Everything after it stays the same.
let
Source = Csv.Document(File.Contents("C:\Finance\Bank\statement-2026-10.csv"), [Delimiter = ",", Columns = 5, Encoding = 65001, QuoteStyle = QuoteStyle.Csv])
in
SourceEncoding = 65001 is UTF-8, the default that Microsoft lists for Csv.Document. If a supplier name with an accent comes through garbled, the bank file is probably in a different encoding; try Encoding = 1252 (Windows-1252), a common encoding for files exported on Windows. For a folder of monthly statements, the guide to combining monthly files shows how to apply the same steps to every file. The post on replacing a monthly copy-paste process walks through that setup.
Putting it together
This query chains the steps above, from the raw text to the ledger rows. The opening balance comes from the statement's own opening row, and the printed closing balance is a step of its own so it can become a parameter.
let
PrintedClosing = 1685.90,
Raw = "Account: 12345678,,,,#(lf)Statement period: 01/10/2026 to 31/10/2026,,,,#(lf)Date,Description,Paid out,Paid in,Balance#(lf)01/10/2026,Opening balance,,,""2,500.00""#(lf)02/10/2026,CARD PAYMENT OFFICE SUPPLIES LTD,45.60,,""2,454.40""#(lf)03/10/2026,BACS ALDER LTD INV-087,,450.00,""2,904.40""#(lf)06/10/2026,DIRECT DEBIT CITY ENERGY,""1,210.00"",,""1,694.40""#(lf)07/10/2026,BANK CHARGES,8.50,,""1,685.90""",
Rules = #table(
type table [Keyword = text, Direction = text, Account = text],
{{"OFFICE SUPPLIES", "out", "6200 Office costs"}, {"ENERGY", "out", "6400 Utilities"}, {"BANK CHARGES", "out", "6900 Bank charges"}, {"BACS", "in", "1100 Trade debtors"}}
),
Csv = Csv.Document(Raw, [Delimiter = ",", Columns = 5, QuoteStyle = QuoteStyle.Csv]),
Promoted = Table.PromoteHeaders(Table.Skip(Csv, each [Column1] <> "Date"), [PromoteAllScalars = true]),
Typed = Table.TransformColumnTypes(Promoted, {{"Date", type date}, {"Paid out", type number}, {"Paid in", type number}, {"Balance", type number}}, "en-GB"),
Opening = Table.SelectRows(Typed, each [Description] = "Opening balance"){0}[Balance],
Moves = Table.SelectRows(Typed, each [Description] <> "Opening balance"),
Amount = Table.AddColumn(Moves, "Amount", each
(if [Paid in] = null then 0 else [Paid in]) - (if [Paid out] = null then 0 else [Paid out]), type number),
Indexed = Table.AddIndexColumn(Amount, "Line", 0, 1),
Previous = {Opening} & Indexed[Balance],
BadLines = Table.SelectRows(Indexed, each Number.Round(Previous{[Line]} + [Amount] - [Balance], 2) <> 0)[Line],
Movement = Number.Round(Opening + List.Sum(Indexed[Amount]) - PrintedClosing, 2),
Account = Table.AddColumn(Indexed, "Account", (row) =>
let
dir = if row[Amount] >= 0 then "in" else "out",
hits = Table.SelectRows(Rules, each Text.Contains(row[Description], [Keyword]) and ([Direction] = dir or [Direction] = "any"))[Account]
in
if List.IsEmpty(hits) then "9999 Suspense" else List.First(hits), type text),
Ledger = Table.SelectColumns(Account, {"Date", "Description", "Account", "Amount"}),
Result =
if not List.IsEmpty(BadLines) then error "Running balance breaks at line " & Text.From(BadLines{0} + 1)
else if Movement <> 0 then error "Movements don't reach the printed closing balance; difference " & Number.ToText(Movement)
else Ledger
in
ResultResult
| Date | Description | Account | Amount |
|---|---|---|---|
| 2026-10-02 | CARD PAYMENT OFFICE SUPPLIES LTD | 6200 Office costs | -45.6 |
| 2026-10-03 | BACS ALDER LTD INV-087 | 1100 Trade debtors | 450 |
| 2026-10-06 | DIRECT DEBIT CITY ENERGY | 6400 Utilities | -1210 |
| 2026-10-07 | BANK CHARGES | 6900 Bank charges | -8.5 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
It returns the same four rows as the table in Step 2. In Excel, keep Rules and PrintedClosing in workbook tables so the finance team can change them without opening the editor.
Before you post to the ledger
The cleaned table is ready to post, or to import into accounting software, once each line has a date, a description, a signed amount and an account. With an account on every line, the same rows can feed the actuals side of a budget vs actual report. Two more checks are worth adding:
- Overlapping downloads create duplicate lines, so filter by date to the statement period.
- Suspense lines should be zero, or reviewed, before posting.
The guide to reconciling two tables covers matching the bank lines against the ledger afterwards, and the exercise on reconciling bank and ledger with a full outer join lets you practise it. On Power Query Exercises, that exercise is checked against hidden data, so a query that only works for the sample rows fails.
Frequently asked questions
Why not let Power Query detect the types automatically?
Power Query Desktop uses your operating system's regional format to interpret text when it converts types, not the bank's format (Data types in Power Query, Microsoft Learn). On a machine set to English (United States), a UK statement's dates are read month first. Setting the culture in the type step makes the query give the same result on every machine.
Should Paid out be negative or should I keep debit and credit columns?
Keep whichever your accounting software imports. A signed amount is easier to total and to check against the balance, and you can split it again at the end with two conditional columns if the import needs debit and credit.
What if the statement has no running balance?
Use the opening and closing balances printed on the PDF statement, or from online banking. The closing-balance check in Step 3 still works; only the row-by-row check needs the Balance column.
Sources
- Csv.Document, Microsoft Learn (learn.microsoft.com)
- Table.TransformColumnTypes, Microsoft Learn (learn.microsoft.com)
- Data types in Power Query, Microsoft Learn (learn.microsoft.com)
About the author
Tahir Nas, software engineer, writes and reviews the lessons, guides and exercises on Power Query Exercises. Tahir has 8 years of experience in tech, working across system administration, cloud architecture, QA, scripting and automation, and software engineering in many languages and mainstream databases. GitHub profile. Every M example shown with a result was run in the site's simulator when the page was built, and the output is that run's result; examples marked for Excel and Power BI only are shown without output. How content is tested.
Keep reading
- Combine marketing exports from several ad platforms in Power QueryCombine ad platform exports with different column names, date formats and number styles into one Power Query table, then calculate CTR and CPC correctly.
- Allocate shared costs across departments with Power QueryAllocate rent, IT and other shared costs in Power Query: driver tables, department shares, a merge that spreads each cost, and rounding that ties out.
- Budget vs actual reporting with Power QueryBuild a budget vs actual report in Power Query: unpivot a wide budget, total actuals by month, full outer join both sides, then label variances correctly.
Practise Power Query in the browser with graded exercises checked against fresh data.
Power Query Exercises is an independent educational platform and is not affiliated with, endorsed by, sponsored by, or approved by Microsoft Corporation. All third-party trademarks belong to their respective owners.