"I connected to my data source, it loaded, and I thought I was done. Then someone showed me Power Query Editor and I realised I had barely started." — Power BI Advanced student, Module 2 orientation
Power BI Desktop is two tools in one body: a report canvas, and a data preparation engine. Most beginners spend 90% of their time on the canvas and wonder why their measures produce wrong numbers. The answer is almost always in the data — and the data is shaped in Power Query Editor. This lesson maps every panel of the Power Query Editor interface and the transformation categories you will use every day as a Power BI analyst.
A chef does not modify ingredients one at a time and hope the dish comes out right. They write a recipe — a sequence of steps — and the dish is the result of executing that recipe in order. Power Query Editor works the same way. Every action you take (filter rows, change type, rename column) is recorded as a step in the Applied Steps pane. The Preview pane shows you the dish at any chosen step. Change one step, and every subsequent step reruns automatically. This is not a spreadsheet — it is a deterministic, repeatable pipeline.
The 5 Interface Panels — What Each One Does
| Panel | Location | What It Shows | Key Use |
|---|---|---|---|
| Queries Pane | Left sidebar | All queries (tables) in this dataset | Navigate between queries; right-click to duplicate, rename, or group queries |
| Formula Bar | Top, below ribbon | The M code for the currently selected Applied Step | View or manually edit the M expression for a step — essential for advanced customisation |
| Preview Pane | Centre | Sample rows at the currently selected step | See the effect of each transformation in real time; click a column header to transform it |
| Applied Steps Pane | Right sidebar | Ordered list of every transformation applied to this query | Navigate history; edit, delete, or reorder steps; click any step to preview data at that point |
| Ribbon Tabs | Top | Categorised transformation commands (Home / Transform / Add Column / View) | Home for structure operations; Transform for column value changes; Add Column for computed columns |
The 5 Core Transformation Categories
Every transformation in Power Query falls into one of five categories. Understanding the category determines which ribbon tab and which approach to use:
Applied Steps — The Most Important Panel in Power Query
The Applied Steps pane is a non-destructive transformation history. Each step is an M expression. Key facts every analyst must know:
- Steps run in order — you cannot reference a column in Step 5 that was renamed in Step 7. Order matters.
- You can click any step to inspect the data at that point — essential for debugging. Click Source to see raw data; click the last step to see the final output.
- The gear icon next to a step opens its settings dialog — so you can edit filter values, change column names, or adjust type assignments without rewriting M.
- Deleting a step removes all transformations that depend on it — Power Query warns you. Know your step dependencies before deleting.
- Auto-generated steps from the UI are valid M code — click a step, read the Formula Bar. This is how you learn M without writing it from scratch.
3 Mistakes Every Power Query Beginner Makes
- Letting Power BI auto-detect data types without reviewing them: The automatic "Changed Type" step generated when you first load a query often gets it wrong — especially for date columns in non-US locale formats and numeric IDs stored as text. Always review the Auto-Detect step immediately and correct any wrong type assignments.
- Transforming data in the source file instead of in Power Query: Editing the source Excel or CSV file before import creates a manual dependency — if someone overwrites the file, all edits are lost. All transformations must live in Power Query steps so they reapply automatically on every refresh.
- Not disabling Load for staging queries: When you create an intermediate query that exists only to feed another query (a staging or lookup table), right-click it in the Queries pane and uncheck "Enable Load". Loading it into the data model wastes memory and clutters the field list in report view.
Quick Knowledge Check
Q1. In the Power Query Editor, which pane shows a list of every transformation applied to a query in order, and allows you to click any step to preview data at that point?
Show Answer
The Applied Steps pane (right sidebar). Each entry represents one M expression. Clicking a step shows the data state after that step runs. The Formula Bar shows the M code for the currently selected step.
Q2. A sales dataset has one column for each month (Jan, Feb, Mar … Dec). You need to create a chart that shows sales over time. Which Power Query operation is required first?
- A) Pivot the month columns to create a wider table
- B) Unpivot the month columns to create "Month" and "Sales" rows
- C) Merge the query with a Date table
- D) Add a Conditional Column based on the month name
Show Answer
B — Unpivot. When months are individual columns (wide format), you cannot plot them on a time axis. Unpivoting converts those columns into two columns: "Month" (attribute) and "Sales" (value). This long format is required for time intelligence in Power BI.
Q3. You have a staging query that transforms raw data to feed a final clean query. The staging query should not appear in the field list in Report view. What should you do?
Show Answer
Right-click the staging query in the Queries pane → uncheck "Enable Load". This keeps the query in Power Query for transformation purposes but prevents it from being loaded into the in-memory data model, saving memory and keeping the report field list clean.
- Power Query is a pipeline, not a spreadsheet — every action becomes an M step; steps run in order; the result is deterministic and repeatable on every refresh.
- Applied Steps pane is your transformation history — click any step to inspect data at that point; use the gear icon to edit step settings without rewriting M.
- Auto-detected types are often wrong — always review the "Changed Type" step Power BI generates automatically, especially for dates and numeric IDs stored as text.
- Merge = JOIN, Append = UNION — merge combines columns from two queries on a matching key; append stacks rows from queries with the same structure.
- Disable Load for staging queries — intermediate queries that feed other queries should not be loaded into the data model; uncheck "Enable Load" to keep the field list clean.