Ravindra BagaleCourses & study guides

9. Get Data from Folder: Monthly Bank Statements Walkthrough

By Last updated:

Mitrano, office madhe dar mahinyala navin file yete – Jan.xlsx, Feb.xlsx... Pratyek mahinyala copy-paste karaycha? Bilkul nahi! Ya module madhe aapan monthly bank statements ekach folder madhun combine karu, aani navin file taakli ki refresh ne automatic yeil ashi vyavastha karu.

What you will learn in this module

  • Combining many files with the same structure from one folder
  • What the helper queries (Parameter, Sample File, Transform Sample File, Transform File) do
  • Cleaning bank-statement quirks: header rows, opening/closing lines, Dr/Cr, amounts with commas
  • Getting the month from Source.Name, skipping hidden/temporary files
  • Handling a file with an extra column or different headers
  • Automatic refresh when next month's file is dropped in (local folder and SharePoint folder)

Every month, Ravindra Bagale downloads his savings-account statement as an Excel file: Jan.xlsx, Feb.xlsx, Mar.xlsx … He wants one Power BI table with all transactions to track spending on Blinkit and Amazon Now, UPI transfers, ATM withdrawals and salary credits. The same technique works for monthly Blinkit order exports (Module 7.20).

Fictional data

The bank ("Example Bank"), account number, narrations, reference numbers and amounts below are invented for practice.

Concepts in this chapter

  1. 9.1The Files and Their Layout
  2. 9.2Connect to the Folder
  3. 9.3Filter Out Hidden, Temporary and Unwanted Files
  4. 9.4Combine Files and the Helper Queries
  5. 9.5Clean One File in Transform Sample File
  6. 9.6Add Month from Source.Name and Categorise Transactions
  7. 9.7A File with an Extra Column or Different Headers
  8. 9.8Automatic Refresh When a New Month Arrives

The chapter recap is at the end of the last concept page.