9. Get Data from Folder: Monthly Bank Statements Walkthrough

9.5 Clean One File in Transform Sample File

By Ravindra Bagale · Last updated: 25 September 2026

// 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

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):

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.