# 9.5 Transform Sample File मध्ये एक file clean करा

Source: https://ravindrabagale.com/mr/powerbi/ch09-get-data-from-folder-monthly-bank-statements/9-5-clean-one-file-in-transform-sample-file.html
Language: mr (Marathi with English technical terms)

Transform Sample File निवडा. Remove Top Rows =4 करा; बदलणाऱ्या layouts साठी खाली header शोधण्याचं code आहे.

Use First Row as Headers करा. Automatic Changed Type पुढे योग्य locale देण्याआधी तपासा / काढा.

Narration मधून Opening Balance आणि Closing Balance वगळा. Date null / empty आणि End of Statement marker काढा.

Debit, Credit, Balance मधले commas काढा. फक्त Debit / Credit मध्ये योग्य अर्थाने null →0 करा.

Date ला Using Locale › English (India); amounts Fixed Decimal करा.

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

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)

Code च्या assumptions तपासा

पहिल्या cell मध्ये Date header शोधला आहे. HeaderPos = −1 मिळाला तर header सापडलेला नाही; स्पष्ट error देऊन ती file तपासा. MissingField.UseNull सोयीचं असलं तरी आवश्यक Debit / Credit column missing असेल तर त्याला शांतपणे 0 करून financial total दाखवू नका. Missing required schema वेगळी flag करा.

Excel मध्ये काही values आधीच dates / numbers असू शकतात; Text.From cleaning साठी मदत करतो. Numeric text conversion करताना localeही source शी जुळवा.

एक Amount आणि Dr/Cr असलेला export

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

 | 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

Code मधला दुसरा भाग Dr/Cr किंवा Amount शेवटच्या DR / CR वरून Side ठरवतो; मग Debit / Credit वेगळे करतो. Marker missing / unknown असेल तर CR गृहित धरू नका. Sample च्या known inputs साठी हा code आहे; production मध्ये unknown Side ला error / flag द्या. Net Amount साठी शेवटी Credit − Debit जोडा.

रवींद्र बागले यांची tip

प्रत्येक file चे headers, Dr/Cr आणि commas नीट करायचे steps Sample File मध्येच ठेवा. Combined query मध्ये title rows काढल्यास फक्त काही files वर योग्य परिणाम होऊ शकतो.