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/FreshGo/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/MarketRush/NASHIK/616100054321 | 616100054321 | 289.00 | 74,749.00 | |
| 15-06-2026 | NEFT-RENT JUN-RAJA | N166600002 | 15,000.00 | 59,749.00 |
About this data: Teaching sample for a fictional shop “HomeNest Supplies”. Figures and names (e.g. Rohan Patil) are made up for this lesson on get data from folder combining monthly bank.
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 (FreshGo + MarketRush); 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 tasks (10+)
Try these on a blank sheet. Open a hint only if you are stuck. No quiz — just practice.
1. Rebuild the smallest example of get data from folder combining monthly bank on a blank sheet for Pearl Textiles.
Hint: Use only the steps on this page. Keep numbers numeric.
2. Change one input for Neha Shah and confirm the result updates.
Hint: If the result is stuck, check absolute vs relative references.
3. Repeat the same steps with a £ or € value for Anna Kowalski.
Hint: Formatting must not break SUM.
4. Write the result meaning in one plain sentence next to the cell.
Hint: Imagine explaining it to a new joiners.
5. Add a second row and fill the method down.
Hint: Double-click the fill handle if the pattern is consistent.
6. Break the example on purpose, then fix it using this lesson.
Hint: Common break: text instead of a number.
7. Apply get data from folder combining monthly bank to a 5-row made-up Pearl Textiles table.
Hint: Keep column headers clear.
8. Check your answer with a simple total or COUNT.
Hint: A wrong total usually means a format or key mismatch.
9. Swap ₹ and $ on one column and re-check totals.
Hint: Stored values stay the same; only the display changes.
10. List two mistakes beginners make with get data from folder combining monthly bank.
Hint: Look back at the tip box on this page.
11. Teach the steps out loud in under one minute.
Hint: Why → What → How.
12. Save the sheet as PearlTextil_practice.xlsx.
Hint: Use a clear file name for class.
Loading practice tasks...
- Rows in the import (Easy): How many rows are in the Order ID column?
- Clean one customer name (Easy): Remove the extra spaces from the first Customer name.
- Fix the case of a city (Easy): Show the first City in normal case, like "Pune".
- Total quantity (Easy): Add up the Qty column.
- How many spaces were extra? (Medium): How many extra spaces does the first Customer name have?
- Duplicate order check (Medium): How many times does Order ID "ORD-502" appear?
- Orders from one city, any case (Medium): How many rows have City "Pune", in any case?
- Amount by channel (Medium): Add up the Amount column for rows where Channel is "App".
- Average order amount (Medium): Find the average of the Amount column, rounded to 0 decimals.
- Rows after Remove Duplicates (Hard): How many rows are left after the extra copies of "ORD-502" are removed?
- Biggest store order (Hard): What is the largest Amount where Channel is "Store"?
- Price per unit (Hard): For the first row, divide Amount by Qty.
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
