📎 This article includes 2 downloadable practice files ↓
In this article
Every month someone opens an export, deletes the top rows, fixes dates, trims spaces, and builds the same report. Power Query records those clean-up steps once; afterwards one click of Refresh repeats them on new data. It’s in Excel 2016 and later (Data tab) and in Power BI.
Import a CSV
- Data › Get Data › From File › From Text/CSV › choose
pq-01-sales.csv. - A preview opens. Click Transform Data (not Load) to open the Power Query Editor.
The Editor
- Ribbon: Home, Transform, Add Column tabs.
- Queries pane (left): every query in the workbook.
- Applied Steps (right): every action recorded, in order. Click a step to see the data at that point; the ✕ deletes it; right-click › Rename to describe it.
- Formula bar (View › Formula Bar): the M code behind each step.
Data types: the first thing to fix
The icon left of each column name shows its type: ABC text, 123 whole number, 1.2 decimal, a calendar for date. Power Query guesses (the “Changed Type” step), and with Indian dd-mm-yyyy dates it often guesses wrong.
For the Date column: click the type icon › Using Locale… › Data type Date, Locale English (India). Now 04-10-2026 is read as 4 October.
Close & Load
Home › Close & Load puts the result in a new sheet as an Excel Table. Close & Load To… offers: Table, PivotTable report, Only Create Connection (for queries that feed other queries), and Add to Data Model (for Power Pivot).
Refresh
Replace the CSV with next month’s file (same name and location), then Data › Refresh All (Ctrl+Alt+F5). All steps run again on the new data. Edit a query later: Data › Queries & Connections › double-click it.
Where beginners go wrong
| Problem | Cause |
|---|---|
| Dates show as errors or months and days swapped | Type set without locale; use Using Locale › English (India) |
| Typing into the loaded table | Gets overwritten on refresh; change the query instead |
| Refresh fails after moving files | Source path changed; Home › Data source settings › Change Source |
| Clicking Load instead of Transform | Loads raw data; edit the query to clean it |
Practice
Download the source file(s) and the expected-results workbook below. Build the query in Excel (Data › Get Data), load it to a sheet, and compare your row count and totals with the Checks sheet.
📎 Practice files for this article
- 🧾pq-01-sales.csv180 orders with dd-mm-yyyy dates.⬇ CSV · 10 KB
- 📗Expected resultsWhat your query should produce, with check totals.⬇ XLSX · 5 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.