7. Data Cleaning A–Z in Power Query
7.20 Combine Files from a Folder (Quick Version)
Start with a problem
April and May orders come as separate files. Zoya appends them by hand each month.
Explain simply
Put same-layout files in one folder and use Get data › Folder › Combine. New files are picked up on refresh. Module 9 is the full walkthrough.
Show the data
| File | Rows | Amount (₹) |
|---|---|---|
| Orders_2026_04.csv | 3 | 900 |
| Orders_2026_05.csv | 5 | 1,200 |
About this data: This table uses columns: File, Rows, Amount (₹). This table uses columns: Rows, Columns include. This table uses columns: Source.Name, Rows, Amount (₹). 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: folder_orders/Orders_2026_04.csv, folder_orders/Orders_2026_05.csv (source: src/practice/).
Demonstrate
- Save both files to one folder.
- Get data › Folder › select it › Combine & Transform Data.
-
Click OK in Combine Files (sample = first file).
Your result should look like this:
Rows Columns include 8 Source.Name, Order ID … Status -
Filter the file list on Extension
.csvbefore combining (hidden/temp files break combines).
Show the result
Your result should look like this (fictional sample):
| Source.Name | Rows | Amount (₹) |
|---|---|---|
| Orders_2026_04.csv | 3 | 900 |
| Orders_2026_05.csv | 5 | 1,200 |
| Total | 8 | 2,100 |
Explain what happened
Power Query built helper queries that apply the sample file's steps to every file and stacked the results.
Explore further
- Extract the month from Source.Name (Module 9).
Detailed reference for this lesson:
When the same export arrives every month (Blinkit_Orders_2025_01.csv, Blinkit_Orders_2025_02.csv …), put all files in one folder and use Home › Get data › More… › Folder. Then filter the file list (Extension, no ~$ temp files) and click Combine Files on the Content column. Power Query creates helper queries and a combined table with a Source.Name column. Steps that must run on each file (removing title rows, renaming headers) go into Transform Sample File.
Module 9 is a full step-by-step walkthrough of this process, using monthly bank statements. It covers the helper queries, deriving the month from the file name, files with an extra column and auto-refresh.
Practice task
Lakshat theva, combine three monthly Amazon Now CSVs from a folder. After reading Module 9, add a File Month column extracted from Source.Name.
Ravindra Bagale's Tip
The most common folder mistake is not filtering out hidden and temporary files (such as ~$Jan.xlsx), which breaks Combine Files. Filter the file list on Extension and Attributes before combining. The full walkthrough is in Module 9. Try it once more, then move on.
Ravindra Bagale's Tip – मराठी
सगळ्यात common folder चूक म्हणजे hidden आणि temporary files (जसं ~$Jan.xlsx) filter न करणं, त्यामुळे Combine Files तुटतं. Combine करण्याआधी file list वर Extension आणि Attributes ने filter लावा. पूर्ण walkthrough Module 9 मध्ये आहे. पुन्हा एकदा करून बघा, मग पुढे जा.
Ravindra Bagale's Tip – हिंदी
सबसे common folder गलती है hidden और temporary files (जैसे ~$Jan.xlsx) को filter न करना, जिससे Combine Files टूट जाता है. Combine करने से पहले file list को Extension और Attributes पर filter करो. पूरा walkthrough Module 9 में है. एक बार फिर करके देखो, फिर आगे बढ़ो.
Fix common mistakes
| Wrong result | Reason | Correction |
|---|---|---|
| Combine fails | ~$ temp or non-CSV file in folder | Filter Extension |
| 10 rows | Header repeated from second file | Promote headers in the sample file transform |
| Different columns | Layout changed | Fix layout or add missing columns |
Practise independently
Practice task
Add a June file with 4 rows worth ₹1,000. What does refresh show?
Hints
- Add.
Solution
12 rows, ₹3,100.
Think like a professional
Folder combines replace monthly manual work – a strong automation example for interviews.
Revision card