9. Get Data from Folder: Monthly Bank Statements Walkthrough
9.1 The Files and Their Layout
Start with a problem
Every month a new Blinkit/Amazon Now orders file lands in a folder. Zoya copies and pastes them into one sheet – slow and error-prone.
Explain simply
If files share the same layout (same columns in the same order), Power BI can combine the whole folder into one table.
Show the data
| File | Rows | Amount (₹) |
|---|---|---|
| Orders_2026_04.csv | 3 | 900 |
| Orders_2026_05.csv | 5 | 1,200 |
| Total | 8 | 2,100 |
About this data: This table uses columns: File, Rows, Amount (₹). This table uses columns: Check, Expected after combine. This table uses columns: A, B, C, D, E, F. This table uses columns: Month, Date, Narration, Ref No, Debit, Credit, Net Amount, Mode. Values shown are the lesson examples already on this page (not a new dataset).
Fictional sample data. Practice file: folder_orders/Orders_2026_04.csv, folder_orders/Orders_2026_05.csv (source: src/practice/).
Demonstrate
- Download both files from
folder_orders/into one folder, e.g.C:\PBI\folder_orders. - Open each file; confirm identical headers.
- Note the expected total: 8 rows, ₹2,100.
- (Module 9.2 connects to the folder.)
Show the result
Your result should look like this (fictional sample):
| Check | Expected after combine |
|---|---|
| Rows | 8 |
| Source.Name values | 2 file names |
| Sum of Amount | ₹2,100 |
Explain what happened
Same columns mean one transform can be applied to every file.
Explore further
- Plan a naming pattern: Orders_YYYY_MM.csv.
Detailed reference for this lesson:
Folder: C:\Finance\BankStatements\ containing Jan.xlsx, Feb.xlsx, Mar.xlsx (sheet name Statement in each).
Each file looks like this (Jan.xlsx, messy export):
| A | B | C | D | E | F |
|---|---|---|---|---|---|
| Example Bank – Statement of Account | |||||
| Account Holder: Ravindra Bagale | |||||
| Account No: XXXXXX4321 · Branch: Kothrud, Pune | |||||
| Period: 01-01-2025 to 31-01-2025 | |||||
| Date | Narration | Ref No | Debit | Credit | Balance |
| Opening Balance | 52,600.00 | ||||
| 01-01-2025 | UPI/DR/Blinkit/Groceries/YESB | UPI500123 | 486.00 | 52,114.00 | |
| 02-01-2025 | NEFT/CR/SALARY JAN/EXAMPLE TECH PVT LTD | NEFT00987 | 65,000.00 | 1,17,114.00 | |
| 05-01-2025 | IMPS/DR/Shraddha Bagale/Rent share | IMPS4455 | 8,000.00 | 1,09,114.00 | |
| 07-01-2025 | ATM WDL/Kothrud Pune | ATM7788 | 2,000.00 | 1,07,114.00 | |
| 10-01-2025 | UPI/DR/Amazon Now/Fruits/HDFC | UPI500456 | 312.50 | 1,06,801.50 | |
| 12-01-2025 | UPI/CR/Salman/Trip split | UPI500789 | 1,500.00 | 1,08,301.50 | |
| Closing Balance | 1,08,301.50 | ||||
| *** End of Statement *** |
Quirks to fix: 4 title rows; an Opening Balance and a Closing Balance line; an end marker; dates in dd-mm-yyyy; amounts as text with Indian commas (1,17,114.00); Debit and Credit in separate columns with blanks.
The goal – clean combined table:
| Month | Date | Narration | Ref No | Debit | Credit | Net Amount | Mode |
|---|---|---|---|---|---|---|---|
| Jan | 01-01-2025 | UPI/DR/Blinkit/Groceries/YESB | UPI500123 | 486.00 | 0 | −486.00 | UPI |
| Jan | 02-01-2025 | NEFT/CR/SALARY JAN/… | NEFT00987 | 0 | 65,000.00 | 65,000.00 | NEFT |
| Jan | 05-01-2025 | IMPS/DR/Shraddha Bagale/Rent share | IMPS4455 | 8,000.00 | 0 | −8,000.00 | IMPS |
| Jan | 07-01-2025 | ATM WDL/Kothrud Pune | ATM7788 | 2,000.00 | 0 | −2,000.00 | ATM |
| Feb | … | … | … | … | … | … | … |
Ravindra Bagale's Tip
Friends, many students assume every monthly statement has the same layout, and the combine breaks when one month's export has an extra header row or a renamed column. Open two or three files side by side before you start and write down the differences. Plan your cleaning for the worst file, not the first one. Don't worry – after doing it two or three times, it becomes a habit.
Ravindra Bagale's Tip – मराठी
मित्रांनो, बऱ्याच students ना वाटतं प्रत्येक monthly statement चा layout सारखाच असतो, आणि एखाद्या महिन्याच्या export मध्ये जास्तीची header row किंवा rename झालेला column आला की combine तुटतं. सुरू करण्याआधी दोन-तीन files शेजारी-शेजारी उघडा आणि फरक लिहून काढा. Cleaning पहिल्या file साठी नाही, सगळ्यात वाईट file साठी plan करा. घाबरू नका, दोन-तीन वेळा केलं की सवय होते.
Ravindra Bagale's Tip – हिंदी
दोस्तों, बहुत से students मान लेते हैं कि हर monthly statement का layout एक जैसा होता है, और किसी महीने के export में extra header row या rename हुआ column आते ही combine टूट जाता है. शुरू करने से पहले दो-तीन files साथ-साथ खोलो और फ़र्क लिख लो. Cleaning पहली file के लिए नहीं, सबसे ख़राब file के हिसाब से plan करो. घबराओ मत, दो-तीन बार करने पर आदत हो जाती है.
Fix common mistakes
| Wrong result | Reason | Correction |
|---|---|---|
| Combined rows = 10 | Header row counted from second file | Use first row as headers in the sample transform |
| Combine fails | Temp file ~$ or a different layout | Filter files first (9.3) |
| Columns misaligned | Column order differs | Fix source layout |
Practise independently
Practice task
If June adds 4 rows worth ₹1,000, what will the combined total be?
Hints
- Add.
Solution
12 rows, ₹3,100.
Think like a professional
Write the expected row count before combining; check it afterwards.
Revision card