9. Get Data from Folder: Monthly Bank Statements Walkthrough

9.5 Clean One File in Transform Sample File

By Ravindra Bagale Last updated: 6 October 2026

Start with a problem

Each statement starts with bank title lines and ends with Closing Balance and End of Statement. Amounts are text with Indian commas, like 1,15,000.00.

Explain simply

Clean one file in Transform Sample File. Every step you add there is copied to all files through the Transform File function.

Show the data

Feb.xlsx, sheet Statement (raw; the Ref No column is not shown):

Row	Date	Narration	Debit	Credit	Balance
1–3	bank title lines
4	(blank row)
5	Date	Narration	Debit	Credit	Balance
6		Opening Balance			1,12,514.00
7	02-02-2026	NEFT/CR/SALARY FEB		65,000.00	1,77,514.00
8	05-02-2026	UPI/DR/Amazon Now	350.00		1,77,164.00
9	10-02-2026	IMPS/DR/Raja/Rent	12,000.00		1,65,164.00
10	20-02-2026	UPI/DR/Blinkit	520.00		1,64,644.00
11		Closing Balance			1,64,644.00
12	End of Statement

About this data: This table uses columns: Row, Date, Narration, Debit, Credit, Balance. This table uses columns: Date, Narration, Debit, Credit. This table uses columns: Debit, Credit. This table uses columns: File, Rows. Values shown are the lesson examples already on this page (not a new dataset).

Fictional sample data. Practice file: bank_statements/Jan.xlsx, bank_statements/Feb.xlsx, bank_statements/Mar.xlsx, bank_statements/notes.txt (source: src/practice/).

Demonstrate

Select Transform Sample File › Home › Remove Rows › Remove Top Rows › 4.

Your result should look like this:

First row now

Date, Narration, Ref No, Debit, Credit, Balance
Home › Use First Row as Headers. Delete any automatic Changed Type step (you will set types with the Indian locale).
Date filter › remove blanks/(null), then Text Filters › Does Not Contain End of Statement. This also removes Opening and Closing Balance, because their Date is blank.

Your result should look like this:

Date Narration Debit Credit

02-02-2026 NEFT/CR/SALARY FEB 65,000.00

05-02-2026 UPI/DR/Amazon Now 350.00

10-02-2026 IMPS/DR/Raja/Rent 12,000.00

20-02-2026 UPI/DR/Blinkit 520.00
Select Debit, Credit, Balance › Transform › Replace Values , → (nothing). Replace null with 0 in Debit and Credit only.

Your result should look like this:

Debit Credit

0 65000.00

350.00 0

12000.00 0

520.00 0
Right-click Date › Change Type › Using Locale… › Date, English (India). Set Debit, Credit and Balance to Fixed decimal number.
Add Column › Custom Column Net Amount = [Credit] - [Debit].

Your result should look like this:

Net Amount

65,000, −350, −12,000, −520
Select the combined query Bank Transactions.

Your result should look like this:

File Rows

Jan.xlsx 3

Feb.xlsx 4

Mar.xlsx Error: Mar has 3 title rows and different headers
Open Advanced Editor on Transform Sample File and replace the code with the robust version under Detailed reference below. It finds the row whose first cell is Date and renames Mar's headers. Click Done.

First row now
Date, Narration, Ref No, Debit, Credit, Balance

Date	Narration	Debit	Credit
02-02-2026	NEFT/CR/SALARY FEB		65,000.00
05-02-2026	UPI/DR/Amazon Now	350.00
10-02-2026	IMPS/DR/Raja/Rent	12,000.00
20-02-2026	UPI/DR/Blinkit	520.00

Debit	Credit
0	65000.00
350.00	0
12000.00	0
520.00	0

Net Amount
65,000, −350, −12,000, −520

File	Rows
Jan.xlsx	3
Feb.xlsx	4
Mar.xlsx	Error: Mar has 3 title rows and different headers

Show the result

Your result should look like this (fictional sample):

File	Rows	Debit (₹)	Credit (₹)
Jan.xlsx	3	2,486	65,000
Feb.xlsx	4	12,870	65,000
Mar.xlsx	2	640	65,000
Total	9	15,996	1,95,000

Explain what happened

The steps in Transform Sample File became the Transform File function, so Jan, Feb and Mar all got the same cleaning. The fixed Remove Top Rows 4 failed for Mar; the robust code finds the header row by content. Balance check: opening ₹50,000 + net ₹1,79,004 (₹1,95,000 − ₹15,996) = Mar closing balance ₹2,29,004.

Explore further

For a statement with one Amount column and a Dr/Cr column, see the Before/After in the detailed reference below.
List.PositionOf makes the header position depend on the data, not on a fixed number.

Detailed reference for this lesson:

Steps in Power BI (select Transform Sample File)

Home › Remove Rows › Remove Top Rows › 4 (bank title lines). Better: see the robust version in the code below, which finds the header row.
Home › Use First Row as Headers. Delete the automatic Changed Type step (we set types later with the Indian locale).
Narration filter › untick Opening Balance and Closing Balance; Date filter › remove (null) and use Text Filters › Does Not Contain End of Statement.
Select Debit, Credit, Balance › Transform › Replace Values › , → (nothing). Replace null with 0 in Debit and Credit only.
Right-click Date › Change Type › Using Locale… › Date, English (India). Set Debit/Credit/Balance to Fixed Decimal Number.
Add Column › Custom Column › Net Amount = [Credit] - [Debit].

// Transform Sample File (robust version)
let
    Source    = Excel.Workbook(Parameter1, null, true),
    Sheet     = Source{[Item = "Statement", Kind = "Sheet"]}[Data],
    // find the row whose first cell is "Date" instead of assuming 4 title rows
    HeaderPos = List.PositionOf(Sheet[Column1], "Date"),
    Body      = Table.Skip(Sheet, HeaderPos),
    Promoted  = Table.PromoteHeaders(Body, [PromoteAllScalars = true]),
    // accept other banks' / months' header names
    Renamed   = Table.RenameColumns(Promoted, {{"Withdrawal Amt.", "Debit"}, {"Deposit Amt.", "Credit"},
                    {"Description", "Narration"}, {"Chq./Ref.No.", "Ref No"}}, MissingField.Ignore),
    // keep a fixed set of columns; missing ones become null, extra ones are dropped
    Selected  = Table.SelectColumns(Renamed, {"Date", "Narration", "Ref No", "Debit", "Credit", "Balance"},
                    MissingField.UseNull),
    OnlyTxns  = Table.SelectRows(Selected, each [Date] <> null and [Date] <> ""
                    and not Text.Contains(Text.From([Date]), "End of Statement")),
    NoCommas  = Table.TransformColumns(OnlyTxns, {
                    {"Debit",   each Number.From(Text.Remove(Text.From(_ ?? "0"), {",", " "})), Currency.Type},
                    {"Credit",  each Number.From(Text.Remove(Text.From(_ ?? "0"), {",", " "})), Currency.Type},
                    {"Balance", each Number.From(Text.Remove(Text.From(_), {",", " "})), Currency.Type}}),
    Typed     = Table.TransformColumnTypes(NoCommas, {{"Date", type date}, {"Ref No", type text}}, "en-IN"),
    Net       = Table.AddColumn(Typed, "Net Amount", each [Credit] - [Debit], Currency.Type)
in
    Net

Two things to watch

Excel may already give real dates or numbers in some cells. Text.From(_) makes the cleaning work for both text and numbers.
If Date is already a real date in the Excel file, the locale step is harmless.

Statements with a single Amount column and Dr/Cr

Some banks export Amount plus a Dr/Cr column (or "486.00 Dr" in one cell):

Before

Date	Narration	Amount	Dr/Cr
01-02-2025	UPI/DR/Blinkit/Milk	64.00	Dr
03-02-2025	NEFT/CR/SALARY FEB	65,000.00	Cr
04-02-2025	UPI/DR/Zoya/Movie	350.00 Dr

After

Date	Narration	Debit	Credit	Net Amount
01-02-2025	UPI/DR/Blinkit/Milk	64	0	−64
03-02-2025	NEFT/CR/SALARY FEB	0	65,000	65,000
04-02-2025	UPI/DR/Zoya/Movie	350	0	−350

DrCr   = Table.AddColumn(Src, "Side", each
            if [#"Dr/Cr"] <> null and [#"Dr/Cr"] <> "" then Text.Upper(Text.Trim([#"Dr/Cr"]))
            else if Text.EndsWith(Text.Upper(Text.Trim(Text.From([Amount]))), "DR") then "DR" else "CR"),
Amt    = Table.AddColumn(DrCr, "Amt", each
            Number.From(Text.Remove(Text.Upper(Text.From([Amount])), {",", " ", "D", "R", "C"})), Currency.Type),
Debit  = Table.AddColumn(Amt,   "Debit",  each if [Side] = "DR" then [Amt] else 0, Currency.Type),
Credit = Table.AddColumn(Debit, "Credit", each if [Side] = "CR" then [Amt] else 0, Currency.Type)

Got it? Cleaning that every file needs goes in Transform Sample File. Once you understand this, folder combine is very simple.

Ravindra Bagale's Tip

Friends, the mistake I see most is cleaning the combined query when the fix belongs in Transform Sample File. Any step that must apply to every file, such as removing bank header rows, splitting Dr/Cr or converting "1,25,000.00" to a number, goes into Transform Sample File so it runs on each file before combining. Clear?

Fix common mistakes

Wrong result	Reason	Correction
Mar.xlsx shows Error	Remove Top Rows 4 assumes every file has 4 title rows	Find the header row with `List.PositionOf` (robust code)
02-03-2026 becomes 3 February	Type changed with a US locale	Change Type › Using Locale › English (India)
Debit will not sum (shows Count)	Commas removed but the column is still text	Set Fixed decimal number after removing commas

Practise independently

Practice task

Using the cleaned table, what are February's total spend (Debit), income (Credit) and net amount?

Hints

February has 4 transaction rows.

Solution

Debit ₹350 + ₹12,000 + ₹520 = ₹12,870; Credit ₹65,000; Net ₹65,000 − ₹12,870 = ₹52,130.

Think like a professional

Add a balance check: opening + net = closing. If it fails for a month, a row was dropped or counted twice, and you catch it before finance does.

Ravi Sir's Cards for this lesson

Clean one file in Transform Sample File. Cleaning after combining is harder because title rows from 12 files are mixed in.

Revision card

मराठी cards