Ravindra BagaleCourses & study guides मराठी Track your progress

9. Get Data from Folder: Monthly Bank Statements Walkthrough

Practice and Answer Key: Combine Files from a Folder

By Last updated:

Suppose we are combining monthly bank statements for a personal budget report. Make a folder D:\Data\Bank with these files (fictional practice data):

File What is inside
Jan.csv 3 rows (below)
Feb.csv 2 rows (below)
~$Feb.xlsx temporary Excel lock file — must be ignored
notes.txt a note — must be ignored

About this data: This table uses columns: File, What is inside. This table uses columns: File, Date, Narration, Debit, Credit. This table uses columns: Month, Rows, Debit (₹), Credit (₹), Net (₹). This table uses columns: What you see, Why, Fix. Values shown are the lesson examples already on this page (not a new dataset).

Columns in every CSV: Date, Narration, Debit, Credit.

File Date Narration Debit Credit
Jan.csv 01-01-2025 UPI/DR/Blinkit/Groceries 486 0
Jan.csv 02-01-2025 NEFT/CR/SALARY JAN 0 65000
Jan.csv 07-01-2025 ATM WDL/Kothrud Pune 2000 0
Feb.csv 03-02-2025 UPI/DR/Swiggy/Dinner 350 0
Feb.csv 14-02-2025 UPI/DR/Netflix/Plan 649 0

Exercise (one under the other):

  1. Get data › Folder › D:\Data\Bank › Transform Data.
  2. Keep only Extension = .csv. Remove names that begin with ~$.
  3. Combine Files on the Content column.
  4. Add Month = the text before "." in Source.Name.
  5. Add Mode: "ATM" if Narration starts with "ATM", else the text before the first "/".
  6. Add Net = Credit − Debit. Set Debit, Credit and Net to Whole number. Date with locale English (India).
  7. Build a matrix: Rows = Month, Values = Debit, Credit, Net.
  8. Now copy a Mar.csv with one row (02-03-2025, UPI/DR/Blinkit/Groceries, 520, 0) into the folder and press Refresh.

Expected result (check your numbers):

Month Rows Debit (₹) Credit (₹) Net (₹)
Jan 3 2,486 65,000 62,514
Feb 2 999 0 −999
Total 5 3,485 65,000 61,515

After adding Mar.csv and refreshing: 6 rows, Mar Net −520, total Debit ₹4,005, total Net ₹60,995. Mode values: UPI, NEFT, ATM.

Answer key (open after you try) 
Source   = Folder.Files("D:\Data\Bank"),
OnlyCsv  = Table.SelectRows(Source, each Text.Lower([Extension]) = ".csv"
               and not Text.StartsWith([Name], "~$")),
-- Combine Files creates the helper queries and the step below for you
Combined = Table.Combine(...),
Month    = Table.AddColumn(Combined, "Month", each Text.BeforeDelimiter([Source.Name], "."), type text),
Mode     = Table.AddColumn(Month, "Mode", each if Text.StartsWith([Narration], "ATM") then "ATM"
               else Text.BeforeDelimiter([Narration], "/"), type text),
Net      = Table.AddColumn(Mode, "Net", each [Credit] - [Debit], Int64.Type)

Do the CSV-level cleaning (types, locale) inside Transform Sample File so that every month gets the same steps.

Common mistakes:

What you see Why Fix
Combine fails with a format error ~$Feb.xlsx or notes.txt was not filtered out Filter Extension and Name before Combine
Month shows "Jan.csv" Source.Name was used as it is Text before delimiter "."
Mode shows "ATM WDL" Text before "/" on the ATM row Add the "starts with ATM" rule first
March does not appear after Refresh Mar.csv saved in a different folder or as .CSV in caps Same folder; use Text.Lower([Extension])

Samajla ka? Filter the folder first, clean one file in Transform Sample File, and every new month just works.

Thodkyaat sangaycha tar (quick recap)

Keep only the right files in the folder, filter out hidden and temp files, do file-level cleaning in Transform Sample File, and do not touch the helper queries. Now let us move on to parameters.