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
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 करो, फिर बहुत आसान लगेगा.