10.3 Get Data from Folder: Combining Monthly Bank Statements
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
- Data › Get Data › From File › From Folder › browse to the folder › OK.
- A preview lists the files (Name, Extension, Date modified…) › click Combine › Combine & Transform Data.
- 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).
- Remove any non-CSV files: filter Extension =
.csv(do this before combining, or in the combined query's early steps). - 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). - Add a Month column: select Date › Add Column › Date › Month › Name of Month (or Start of Month).
- Add Txn Type with Add Column › Custom Column (formula below).
- Add Net = Credit − Debit (Add Column › Custom Column
[Credit] - [Debit]). - Close & Load To… › PivotTable Report – summarise Debit by Txn Type and Month.
- Next month, drop
Stmt_2026_07.csvinto 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
If the folder has an Excel "notes" file or a hidden temporary file (~$…), the combine fails – many students don't understand what the error means. Right at the start, filter Extension = .csv and remove files whose names start with ~$. Also check once that all the monthly files have the same columns.
Ravindra Bagale's Tip – मराठी
Folder मध्ये एखादी Excel "notes" file किंवा लपलेली temporary file (~$…) असेल तर combine fail होतं – बऱ्याच students ना error चा अर्थ कळत नाही. सुरुवातीलाच Extension = .csv filter लावा आणि नाव ~$ ने सुरू होणाऱ्या files काढा. सगळ्या monthly files same columns च्या आहेत का, हे पण एकदा check करा.
Ravindra Bagale's Tip – हिंदी
Folder में कोई Excel "notes" file या छुपी temporary file (~$…) हो तो combine fail हो जाता है – बहुत से students को error का मतलब समझ नहीं आता. शुरू में ही Extension = .csv filter लगाओ और ~$ से शुरू होने वाले नाम की files हटाओ. सारी monthly files में same columns हैं या नहीं, यह भी एक बार check करो.
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.