9. Get Data from Folder: Monthly Bank Statements Walkthrough
9.7 A File with an Extra Column or Different Headers
Start with a problem
Mar.xlsx arrives with a new layout: 3 title rows, a Value Date column, and Withdrawal Amt./Deposit Amt. instead of Debit/Credit. Shahrukh's report shows an error for March.
Explain simply
Make the file function defensive: find the header row, rename alternative headers, and select a fixed list of columns.
Show the data
| File | Title rows | Columns |
|---|---|---|
| Jan.xlsx, Feb.xlsx | 4 (including a blank row) | Date, Narration, Ref No, Debit, Credit, Balance |
| Mar.xlsx | 3 | Date, Value Date, Description, Chq./Ref.No., Withdrawal Amt., Deposit Amt., Balance |
About this data: This table uses columns: File, Title rows, Columns. This table uses columns: File, Rows, Debit (₹), Credit (₹). This table uses columns: Symptom, Why. This table uses columns: Wrong result, Reason, Correction. Values shown are the lesson examples already on this page (not a new dataset).
Fictional sample data. Practice file: bank_statements/Jan.xlsx, bank_statements/Feb.xlsx, bank_statements/Mar.xlsx, bank_statements/notes.txt (source: src/practice/).
Demonstrate
-
In Bank Transactions, filter Source.Name = Mar.xlsx with the simple version of Transform Sample File.
Your result should look like this:
Symptom Error: the column 'Date' of the table wasn't found (or Debit/Credit null for March) -
Select Transform Sample File › Advanced Editor.
-
Replace the fixed skip with
HeaderPos = List.PositionOf(Sheet[Column1], "Date")andBody = Table.Skip(Sheet, HeaderPos).Your result should look like this:
Header row found Jan, Feb and Mar: the row whose first cell is Date, wherever it is -
After promoting headers, add
Table.RenameColumns(…, {{"Withdrawal Amt.", "Debit"}, {"Deposit Amt.", "Credit"}, {"Description", "Narration"}, {"Chq./Ref.No.", "Ref No"}}, MissingField.Ignore).Your result should look like this:
Mar columns now Date, Value Date, Narration, Ref No, Debit, Credit, Balance -
Add
Table.SelectColumns(…, {"Date", "Narration", "Ref No", "Debit", "Credit", "Balance"}, MissingField.UseNull)› Done.Your result should look like this:
Mar.xlsx rows 02-03-2026 NEFT/CR/SALARY MAR, Credit 65,000 · 04-03-2026 UPI/DR/Blinkit, Debit 640 -
Clear the Source.Name filter and check the row count per file.
Show the result
Your result should look like this (fictional sample):
| File | Rows | Debit (₹) | Credit (₹) |
|---|---|---|---|
| Jan.xlsx | 3 | 2,486 | 65,000 |
| Feb.xlsx | 4 | 12,870 | 65,000 |
| Mar.xlsx | 2 | 640 | 65,000 |
| Total | 9 | 15,996 | 1,95,000 |
Explain what happened
The function now adapts to each file. It finds the header row by content, renames alternative headers (and ignores names a file does not have), and keeps a fixed list of columns, so Value Date is dropped and no column is missing.
Explore further
MissingField.UseNulladds a missing column as null instead of failing. That helps when one month has no Ref No column.
Detailed reference for this lesson:
In Mar.xlsx, the bank added a Value Date column and renamed Debit/Credit to Withdrawal Amt./Deposit Amt.. Without protection, you would see one of these:
| Symptom | Why |
|---|---|
| Debit and Credit are null for all March rows | Headers differ, so Append/Combine creates new columns Withdrawal Amt. and Deposit Amt. |
| Expression.Error: The column 'Debit' of the table wasn't found | A later step (e.g. Changed Type) refers to a header that is missing in that file |
| An unexpected Value Date column appears (null for Jan/Feb) | The expand step includes every column found |
Fixes (already used in 9.5): Table.RenameColumns(…, MissingField.Ignore) maps alternative names, and Table.SelectColumns(…, MissingField.UseNull) keeps exactly the columns you want. In the combined query, the expand step should list the same fixed column names.
Ravindra Bagale's Tip
Look, friends: putting file-specific fixes in the combined query is a common mistake, and so is forgetting that the sample file is simply the first file, so deleting Jan.xlsx changes the sample. Put fixes in Transform Sample File and select columns by name. Password-protected statement files cannot be combined, so save unprotected copies. Practise, and it will feel very easy.
Ravindra Bagale's Tip – मराठी
हे बघा मित्रांनो: file-specific दुरुस्त्या combined query मध्ये टाकणं ही common चूक आहे, आणि sample file म्हणजे फक्त पहिली file आहे हे विसरणं ही पण – त्यामुळे Jan.xlsx delete केली की sample बदलते. दुरुस्त्या Transform Sample File मध्ये करा आणि columns नावाने select करा. Password-protected statement files combine होत नाहीत, म्हणून unprotected copies save करा. Practice करा, मग एकदम सोपं वाटेल.
Ravindra Bagale's Tip – हिंदी
देखो दोस्तों: file-specific सुधार combined query में डालना common गलती है, और यह भूल जाना भी कि sample file बस पहली file होती है – इसलिए Jan.xlsx delete करने पर sample बदल जाती है. सुधार Transform Sample File में करो और columns नाम से select करो. Password-protected statement files combine नहीं होतीं, इसलिए unprotected copies save करो. Practice करो, फिर बहुत आसान लगेगा.
Fix common mistakes
| Wrong result | Reason | Correction |
|---|---|---|
| Value Date appears, null for Jan and Feb | The expand step took every column it found | SelectColumns with a fixed list |
| Expression.Error: the column 'Debit' wasn't found | Changed Type ran before the rename | Rename first (MissingField.Ignore), set types later |
| A fix in the combined query repairs Mar but breaks Jan | File-specific fix placed after the combine | Put layout fixes in Transform Sample File |
Practise independently
Practice task
A future bank file uses Txn Date instead of Date. Which two changes does the robust code need?
Hints
- One change finds the header row; one renames the column.
Solution
1) Find the header with either name: List.PositionOfAny(Sheet[Column1], {"Date", "Txn Date"}). 2) Add {"Txn Date", "Date"} to the RenameColumns list.
Think like a professional
Banks change export layouts without warning. Defensive code (find, rename, select) turns a broken month-end into a non-event.
Revision card