9. Get Data from Folder: Monthly Bank Statements Walkthrough
9.4 Combine Files and the Helper Queries
Steps in Power BI
- Click the Combine Files button (two down-arrows) in the header of the Content column.
- In the Combine Files dialog, Sample File: First file (Jan.xlsx). In the left list, select the sheet Statement (choose a sheet by name, not "Parameter1" object index) › OK.
- Power Query creates a group Transform File from BankStatements with helper queries, plus the combined query (rename it Bank Transactions).
| Helper query | What it is | What you do with it |
|---|---|---|
| Parameter1 | A parameter of type Binary that holds one file's content | Nothing – leave it |
| Sample File | The content of the first file (Jan.xlsx) | Nothing – it feeds the parameter |
| Transform Sample File | A normal query that opens the sample file's Statement sheet | Put all per-file cleaning steps here – you see the data while you work |
| Transform File | A function generated from Transform Sample File | Nothing – it updates automatically and is called once per file |
| Bank Transactions (combined) | Calls Transform File for every file and expands the results; adds Source.Name | Steps that apply to the combined table (month, types, categories) |
Where do I clean?
Anything that depends on the layout of one file (title rows, header row, end markers, renaming headers) goes into Transform Sample File. Anything about the combined data (month column, final types, categories, dedupe across files) goes into the combined query.
Helper queries baghun ghabru naka – Power BI ne te tumchyasathi banvle aahet. Fakt tyanna haat lavu naka.
Ravindra Bagale's Tip
Friends, many students delete or rename the helper queries (Parameter, Sample File, Transform File) because they look like clutter, and the combine stops working. Leave them alone and put them in a group. If you need to tidy up, rename the group, not the helper queries. It's very simple – just make it a habit.
Ravindra Bagale's Tip – मराठी
मित्रांनो, helper queries (Parameter, Sample File, Transform File) पसारा वाटतात म्हणून बरेच students त्या delete किंवा rename करतात, आणि combine चालायचं थांबतं. त्यांना हात लावू नका, एका group मध्ये ठेवा. आवराआवर करायची असेल तर group rename करा, helper queries नाही. एकदम simple आहे, फक्त सवय लावून घ्या.
Ravindra Bagale's Tip – हिंदी
दोस्तों, helper queries (Parameter, Sample File, Transform File) फ़ालतू लगती हैं इसलिए बहुत से students उन्हें delete या rename कर देते हैं, और combine काम करना बंद कर देता है. उन्हें मत छेड़ो, एक group में रख दो. सफ़ाई करनी हो तो group का नाम बदलो, helper queries का नहीं. बहुत आसान है, बस आदत बना लो.