Ravindra BagaleCourses & study guides

9. Get Data from Folder: Monthly Bank Statements Walkthrough

9.5 Clean One File in Transform Sample File

By Last updated:

Steps in Power BI (select Transform Sample File)

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

Samjla ka? Pratyek file la lagnara cleaning Transform Sample File madhe. He ekda samajla ki folder combine ekdum simple aahe.

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?