9. Get Data from Folder: Monthly Bank Statements Walkthrough
9.7 A File with an Extra Column or Different Headers
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
He bagha, mitrano: 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. Practice kara, mag ekdum sope vatel.