Ravindra BagaleCourses & study guides

9. Get Data from Folder: Monthly Bank Statements Walkthrough

9.6 Add Month from Source.Name and Categorise Transactions

By Last updated:

Now select the combined query Bank Transactions. It has a Source.Name column ("Jan.xlsx").

Steps in Power BI

  1. Select Source.Name › Add Column › Extract › Text Before Delimiter › . → rename Month.
  2. Add a month number for sorting: Add Column › Custom Column › Month No = List.PositionOf({"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"}, [Month]) + 1.
  3. (Better practice): name files 2025-01.xlsx, 2025-02.xlsx … so they sort correctly and include the year. Or simply derive the month from the transaction Date in the Date table.
  4. Mode: Add Column › Conditional Column › Narration begins with UPI → UPI; NEFT → NEFT; IMPS → IMPS; ATM → ATM; else Other.
  5. Category: a Custom Column (below) that looks for keywords. For many rules, use a keyword mapping table instead (like CityMap in Module 7.9).
  6. Check the combined query's own Changed Type step. If it refers to a column that no longer exists, delete that step and set the types again.
Month    = Table.AddColumn(Src, "Month", each Text.BeforeDelimiter([Source.Name], "."), type text),
Mode     = Table.AddColumn(Month, "Mode", each Text.BeforeDelimiter(Text.Upper([Narration]), "/"), type text),
Category = Table.AddColumn(Mode, "Category", each
              let n = Text.Upper([Narration]) in
              if Text.Contains(n, "BLINKIT") or Text.Contains(n, "AMAZON NOW") then "Quick Commerce"
              else if Text.Contains(n, "SALARY") then "Salary"
              else if Text.StartsWith(n, "ATM") then "Cash Withdrawal"
              else if Text.Contains(n, "RENT") then "Rent"
              else "Other", type text)

Useful measures once the table is loaded:

Total Spend    = SUM('Bank Transactions'[Debit])
Total Income   = SUM('Bank Transactions'[Credit])
Net Savings    = [Total Income] - [Total Spend]
Quick Commerce Spend = CALCULATE([Total Spend], 'Bank Transactions'[Category] = "Quick Commerce")

Ravindra Bagale's Tip

Mitrano, khup students take the month from the transaction Date and then find that a statement's first rows belong to the previous month. Decide which you need: the statement month (from Source.Name) or the transaction month (from Date). Keep Source.Name until you have checked that every file loaded. Chuk karu naka!