Power Query

01The problem

The orders table has quantity, price and discount, but no revenue. You could calculate it later in DAX, but a clean, well-typed revenue column at the source keeps the model simple. Power Query is where data gets shaped before it reaches the model, and every step you take is recorded, so it runs again on every refresh.

02The concept

Open it: Home → Transform data. The Power Query Editor shows:

Transformations you'll use constantly

Column profiling. View → Column quality / Column distribution / Column profile show valid, error and empty percentages, distinct counts and value frequencies. By default it profiles only the first 1,000 rows: click "Column profiling based on top 1000 rows" in the status bar and choose the entire data set.

Merge and append

Two transformations combine queries, and they're easy to mix up:

A merge asks for a join kind. The ones you'll use:

A left anti merge is a quick data-quality check: if it returns any rows, some orders point to products that don't exist.

How Power Query records your work

Every click becomes a step written in M, Power Query's language. You rarely type M, but reading it helps:

= Table.AddColumn(#"Changed Type", "revenue", each [quantity] * [unit_price] * (1 - [discount_pct] / 100), type number)

Each step takes the previous step's result and passes its own result on, like a recipe. That's why order matters: delete or move a step and every step after it may break.

Good habits with steps

1. Set types early, straight after loading, and check them: a number column typed as text causes problems in every step after.
2. Rename steps that matter (right-click → Rename): Added revenue is clearer than Added Custom six months later.
3. Remove columns you don't need, early. Smaller tables load faster.
4. Don't edit in Excel first. Do every change in Power Query, so it repeats on the next refresh instead of being lost.
5. Check after each important step: row counts in the status bar, column quality at 100% valid.

Close & Apply (Home) saves your steps and loads the result into the model.

This is the Power Query Editor with Kolanut's three queries:

1. Queries pane: one query per table.
2. Formula bar: the M code for the selected step.
3. Column quality: Valid, Error and Empty percentages for each column.
4. Applied Steps: every change, in order.
5. Status bar: Column profiling based on top 1000 rows. Click it to profile the whole table.
6. The ribbon: Home, Transform and Add Column hold the transformations.

03Example

The revenue custom column shown above is exactly what the Custom Column dialog writes for you. In the dialog you only enter:

[quantity] * [unit_price] * (1 - [discount_pct] / 100)

04Walkthrough

1. Home → Transform data. Select the orders query.

2. View → tick Column quality and Column distribution, then switch profiling to the entire data set. All columns should be 100% valid.

3. Add Column → Custom Column. Name: revenue. Formula: as above. OK.

   

4. Click the ABC123 icon on the new column's header → Decimal Number (or Fixed decimal number, good for currency). The result:

   

5. Merge the product category in:

   • With orders selected, Home → Merge Queries.
   • Choose products as the second table; click product_id in both; Join Kind Left Outer. OK.
   • A new column of nested tables appears. Click its expand icon (↔), untick all but category, untick Use original column name as prefix. OK.

6. Look at Applied Steps: each action is a step, in order.

7. Home → Close & Apply. Save.

05Practice

06More practice

Optional drills. They don't count towards the certificate, but they're the fastest way to make this lesson stick. Several use a different dataset from the lesson on purpose: if you can do the same thing on unfamiliar data, you've really learned it.

07Check your understanding