Ravindra BagaleCourses & study guides

10. Power Query in Excel

10.3 Get Data from Folder: Combining Monthly Bank Statements

By Last updated:

A classic use case: every month the bank gives a CSV statement with the same columns. Put them in one folder and combine them once.

Folder D:\Statements\SavingsAC\ (fictional account of Shraddha Bagale): Stmt_2026_04.csv, Stmt_2026_05.csv, Stmt_2026_06.csv…

Sample rows (fictional):

Date Narration Ref No Debit Credit Balance
01-06-2026 NEFT-SALARY JUN 2026-ACME ANALYTICS PVT LTD N152600001 65,000.00 82,450.00
02-06-2026 UPI/BLINKIT/PUNE/615300012345 615300012345 412.00 82,038.00
04-06-2026 ATM WDL/HADAPSAR PUNE A0604001 5,000.00 77,038.00
07-06-2026 IMPS/P2P/RUHI BAGALE/615800098765 615800098765 2,000.00 75,038.00
10-06-2026 UPI/AMAZON NOW/NASHIK/616100054321 616100054321 289.00 74,749.00
15-06-2026 NEFT-RENT JUN-RAJA N166600002 15,000.00 59,749.00

Steps in Excel

  1. Data › Get Data › From File › From Folder › browse to the folder › OK.
  2. A preview lists the files (Name, Extension, Date modified…) › click Combine › Combine & Transform Data.
  3. In Combine Files, choose the sample file (first file) › check delimiter › OK. Power Query creates helper queries (Sample File, Transform Sample File, Transform File) and one combined query with a Source.Name column (the file name).
  4. Remove any non-CSV files: filter Extension = .csv (do this before combining, or in the combined query's early steps).
  5. Change types: Date → Using Locale › English (India); Debit, Credit, Balance → Fixed decimal number; replace nulls in Debit/Credit with 0 (Transform › Replace Values null → 0).
  6. Add a Month column: select Date › Add Column › Date › Month › Name of Month (or Start of Month).
  7. Add Txn Type with Add Column › Custom Column (formula below).
  8. Add Net = Credit − Debit (Add Column › Custom Column [Credit] - [Debit]).
  9. Close & Load To… › PivotTable Report – summarise Debit by Txn Type and Month.
  10. Next month, drop Stmt_2026_07.csv into the folder › Data › Refresh All. Done.

Custom column formula (Power Query M) – order matters, SALARY is checked before NEFT:

= if Text.Contains([Narration], "SALARY") then "Salary"
  else if Text.StartsWith([Narration], "UPI") then "UPI"
  else if Text.StartsWith([Narration], "NEFT") then "NEFT"
  else if Text.StartsWith([Narration], "IMPS") then "IMPS"
  else if Text.StartsWith([Narration], "ATM") then "ATM"
  else "Other"

Result for June (sample rows): Salary credit ₹65,000; UPI ₹701 (Blinkit + Amazon Now); ATM ₹5,000; IMPS ₹2,000; NEFT ₹15,000.

Ravindra Bagale's Tip

Folder madhe ekhadi Excel "notes" file kiwa lapleli temporary file (~$…) asel tar combine fail hota – khup students la error cha arth kalat nahi. Suruvatilach Extension = .csv filter lava aani file names ne suru honari ~$ files kadha. Sagle monthly files same columns cha aahet ka, he pan ekda check kara.

Practice task

Create three monthly CSV statements (April–June 2026, fictional) with at least 10 rows each. Combine them from a folder, classify Txn Type, and build a PivotTable of monthly spend by type. Add July's file and refresh.