Concept
1. The idea in one line
Power Query = a recorder for data cleaning. You import data, clean it with clicks, and Power Query remembers every step. Next month: drop in new files, click Refresh, done.
It's built into Excel 2016+ and Microsoft 365 (Windows; Mac has most features in recent versions) under Data → Get & Transform Data. The same engine powers Power BI.
2. ETL in Excel
| Stage | Meaning | Example |
|---|---|---|
| Extract | get data | 24 monthly CSVs from a folder |
| Transform | clean and shape | trim spaces, fix dates, add product names |
| Load | send it somewhere | Excel Table, pivot, or the Data Model |
Your original files are never changed.
3. Editor tour
Data → Get Data → From File → From Text/CSV → pick a file → Transform Data. The Power Query Editor opens:
| Part | What it is |
|---|---|
| Queries pane (left) | every query in the workbook |
| Preview (middle) | first 1,000 rows, after the selected step |
| Applied Steps (right) | the recorded steps — click one to see data at that point |
| Formula bar | the M code of the selected step (View → Formula Bar) |
| Column header icons | the data type: ABC text, 123 whole number, 1.2 decimal, 📅 date |
| Ribbon | Home, Transform, Add Column, View |
Delete a step with the ✕, edit one with its ⚙ gear. Close & Load sends the result to Excel.
4. Power Query vs formulas
| Use Power Query when… | Use formulas when… |
|---|---|
| the same cleaning repeats every week/month | it's a one-off calculation |
| data comes from many files or systems | data is already a clean table |
| you need to combine, unpivot, de-duplicate, reshape | you need live results as users type |
| data is too big for helper columns (lakhs of rows) | results must update without Refresh |
| you want an auditable list of steps | the logic is "what-if" (scenarios, inputs) |
Typical combination: Power Query prepares a clean table → pivots and formulas analyse it.
5. What Power Query isn't
- Not live: results change only on Refresh.
- Not for data entry: don't type into a loaded query table (your edits disappear on refresh — keep manual columns in a separate table).
- Not a formula replacement for cell-by-cell what-if models.
6. A tiny first query
- Data → From Table/Range (on
tblSales). - Select Region → Transform → Format → UPPERCASE.
- Home → Group By → Region, Sum of Amount.
- Close & Load → a new sheet with NORTH 5,09,000, SOUTH 2,59,000, WEST 3,02,000.
Add a row to
tblSales, click Refresh All — the summary updates.
Common mistakes
Editing the loaded output by hand. Expecting automatic updates without Refresh. Doing everything in Power Query, including things a pivot does better.