Ravindra BagaleCourses & study guides मराठी Track your progress

9. Get Data from Folder: Monthly Bank Statements Walkthrough

9.6 Add Month from Source.Name and Categorise Transactions

By Last updated:

Start with a problem

Shahrukh has 9 clean transactions. Finance asks: how much did we spend each month, and how much went to quick commerce?

Explain simply

Select the combined query and add three columns: Month (from Source.Name), Mode (UPI, NEFT, …) and Category (keyword rules). Then write simple measures.

Show the data

Cleaned Bank Transactions (from 9.5):

Source.Name Date Narration Debit (₹) Credit (₹)
Jan.xlsx 01-01-2026 NEFT/CR/SALARY JAN 0 65,000
Jan.xlsx 03-01-2026 UPI/DR/Blinkit 486 0
Jan.xlsx 07-01-2026 ATM WDL/Kothrud 2,000 0
Feb.xlsx 02-02-2026 NEFT/CR/SALARY FEB 0 65,000
Feb.xlsx 05-02-2026 UPI/DR/Amazon Now 350 0
Feb.xlsx 10-02-2026 IMPS/DR/Raja/Rent 12,000 0
Feb.xlsx 20-02-2026 UPI/DR/Blinkit 520 0
Mar.xlsx 02-03-2026 NEFT/CR/SALARY MAR 0 65,000
Mar.xlsx 04-03-2026 UPI/DR/Blinkit 640 0

About this data: This table uses columns: Source.Name, Date, Narration, Debit (₹), Credit (₹). This table uses columns: Month, Month No. This table uses columns: Mode, Rows. This table uses columns: Category, 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

  1. Select Source.Name › Add Column › Extract › Text Before Delimiter › . › rename Month.

    Your result should look like this:

    Month
    Jan ×3, Feb ×4, Mar ×2

  2. Add Column › Custom Column Month No = List.PositionOf({"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"}, [Month]) + 1.

    Your result should look like this:

    Month Month No
    Jan 1
    Feb 2
    Mar 3

  3. Custom Column Mode = Text.BeforeDelimiter(Text.Upper([Narration]), "/").

    Your result should look like this:

    Mode Rows
    NEFT 3
    UPI 4
    IMPS 1
    ATM WDL 1

  4. Add the Category custom column from the code in the detailed reference (keywords BLINKIT / AMAZON NOW, SALARY, ATM, RENT).

    Your result should look like this:

    Category Rows
    Salary 3
    Quick Commerce 4
    Cash Withdrawal 1
    Rent 1

  5. Set types (Month text, Month No whole number) › Close & Apply. In the report, sort Month by Month No.

  6. Create the measures Total Spend, Total Income, Net Savings and Quick Commerce Spend (code below).

Show the result

Your result should look like this (fictional sample):

Month Total Spend (₹) Total Income (₹) Net Savings (₹) Quick Commerce Spend (₹)
Jan 2,486 65,000 62,514 486
Feb 12,870 65,000 52,130 870
Mar 640 65,000 64,360 640
Total 15,996 1,95,000 1,79,004 1,996

Explain what happened

Month came from the file name, so each statement lands in its own month. Category used keyword rules row by row. The measures then added Debit and Credit for each month.

Explore further

  • File names like 2026-01.xlsx carry the year and sort correctly without Month No.
  • Many keyword rules? Keep a mapping table (Keyword, then Category) and merge it, like CityMap in 7.9.
  • A Conditional Column (begins with ATM → ATM) gives a cleaner Mode label than ATM WDL.

Detailed reference for this lesson:

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

Friends, many 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. Don't make this mistake!

Fix common mistakes

Wrong result Reason Correction
Months sort Feb, Jan, Mar Month is text, sorted A–Z Sort by column › Month No
Blinkit spend counted as Other Keyword check is case-sensitive (Blinkit vs BLINKIT) Compare against Text.Upper([Narration])
Expression.Error: column 'Column7' not found An old Changed Type step in the combined query Delete it and set the types again

Practise independently

Practice task

Which category is the biggest spend in the quarter, and what share of total spend is quick commerce?

Hints
  • Spend = Debit only; Salary is income, not spend.
Solution

Rent ₹12,000 is the biggest: 75.0% of ₹15,996. Quick Commerce ₹1,996 ÷ ₹15,996 = 12.5%. Cash Withdrawal ₹2,000 = 12.5%.

Think like a professional

Keep the keyword rules where finance can read and edit them (a small mapping table). The rules will change more often than your query.

Ravi Sir's Cards for this lesson

Add month from the file name and categorise. After stacking, you must know which month each row came from.Revision card

मराठी cards