📎 This article includes 2 downloadable practice files ↓
In this article
Power Query can calculate new columns too: GST, totals, size bands, flags. You can do most of it with dialog boxes; a little of the M language behind them makes you much more capable.
Conditional Column (no code)
Add Column › Conditional Column:
- If Amount is less than 10000 → Small
- Else if Amount is less than 50000 → Medium
- Else → Large
Like a nested IF, checked top to bottom.
Custom Column (a formula)
Add Column › Custom Column. Double-click column names on the right to insert them.
GST: Number.Round([Amount] * 0.18, 2)
Total: [Amount] + [GST]
Label: [Customer] & " - " & [Region]
Size: if [Amount] >= 50000 then "Large" else if [Amount] >= 10000 then "Medium" else "Small"Column references go in square brackets. Text joins with &. M is case-sensitive: if, then, else in lowercase; function names like Number.Round exactly as written.
Column From Examples
Add Column › Column From Examples: type what you want in the first rows (“Sharma” from “Sharma Traders”) and Power Query writes the formula, like Flash Fill but refreshable.
Read the formula bar
Each step is one line of M, for example:
= Table.AddColumn(#"Changed Type", "GST", each Number.Round([Amount] * 0.18, 2), type number)each means “for each row”; #"Changed Type" is the previous step. Home › Advanced Editor shows the whole query; you’ll rarely need to write it from scratch, but reading it helps when fixing errors.
Handling nulls
[Qty] * [Rate] gives null if either is null. Use if [Qty] = null then 0 else [Qty] * [Rate], or try [Qty] * [Rate] otherwise 0 to catch errors.
Where beginners go wrong
| Error | Cause |
|---|---|
| Expression.Error: The name ‘If’ wasn’t recognized | M keywords are lowercase: if |
| Can’t apply operator & to text and number | Convert: Text.From([Qty]) |
| Column loads as text | Set the type after creating 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-07-sales.csvThe same 180 orders as Lesson 7.⬇ CSV · 10 KB
- 📗Expected resultsWhat your query should produce, with check totals.⬇ XLSX · 12 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.