Concept
1. One CSV file
Data → Get Data → From File → From Text/CSV → choose the file. The preview dialog has three settings:
| Setting | Check |
|---|---|
| File Origin | 65001: Unicode (UTF-8) for most exports — wrong origin shows ₹ or Hindi text as junk |
| Delimiter | Comma (or Tab / Semicolon / Custom) |
| Data Type Detection | "Based on first 200 rows" is default; choose "Do not detect" if you'll set types yourself |
Click Transform Data (not Load) so you can check it first.
2. A folder — the killer feature
Capstone data: data/ with 24 files sales_2024-04.csv … sales_2026-03.csv, all with the same columns (OrderID, OrderDate, StoreID, ProductID, Qty, UnitPrice, Discount).
- Data → Get Data → From File → From Folder → choose
data. - A list of files appears → Combine → Combine & Transform Data.
- Choose the sample file (first file is fine) → OK.
Power Query creates helper queries (Sample File, Transform Sample File, a function) and one combined query with 50,000 rows plus a Source.Name column holding the file name.
Next month, drop sales_2026-04.csv into the folder → Refresh → it's included. No copy-paste ever again.
3. Keep the file name (it's useful)
Source.Name tells you which file each row came from — great for checking. You can also derive the month from it: Add Column → Extract → Text Between Delimiters ("_" and ".").
4. Filter the folder
The folder may contain other files (a README, an old .xlsx, ~$ temp files). In the first step's file list, filter Extension = .csv before combining — or later, edit the Source step and add a filter. One wrong file with different columns breaks the whole combine.
5. Excel files in a folder
Same steps; in the combine dialog pick the sheet or table name to use from each file. Works best when every file has the same sheet name or a Table with the same name.
6. Web table
Data → From Web → paste a URL with an HTML table (e.g. a Wikipedia list page) → the Navigator shows detected tables (Table 0, Table 1 …) → pick one → Transform Data.
- Works for static HTML tables; pages built by JavaScript may show nothing.
- Respect the site's terms; don't refresh every minute.
- For APIs that return JSON, use From Web too — Power Query parses JSON into records you can expand.
7. Other sources (just know they exist)
From Table/Range, From Workbook, From PDF (Microsoft 365, Windows), From SharePoint Folder (OneDrive for Business), From Database (SQL Server, MySQL with connector), From ODBC.
Common mistakes
Choosing "Load" directly and missing type problems. Junk characters from the wrong File Origin. A stray non-CSV file in the folder. Files with different column names (fix at source or rename in the sample transform).