Adding Conditional & Custom Columns
Adding columns is where Power Query starts feeling like programming. There are three routes — the Conditional Column dialog, Custom Column with M, and index columns — and a fourth question sitting behind all of them: should this be a column at all, or a DAX measure?
Learning Objective
The mechanics are easy. The judgement — which route, and whether a column is the right answer in the first place — is what this lesson is really about.
- Build banding and categorisation logic with the Conditional Column dialog
- Write Custom Columns in M, including if/then/else and null-safe expressions
- Add index columns, and know where they must sit in a query
- Apply the column-versus-measure decision rule correctly
- Explain when logic belongs in Power Query rather than DAX, and why
Why This Matters
A concrete case. You have a Discount column holding rates from 0 to 0.30. Nobody wants to slice by 0.05 and 0.15 separately — they want "no discount", "light", "heavy".
That banding has to exist somewhere. It could be a Power Query column, a DAX calculated column, or a DAX measure with SWITCH. All three work. Only one is right, and choosing wrongly costs either model size or the ability to use it as a slicer.
If you want to aggregate it, it should be a measure. Measures are calculated at query time and cost no storage.
And if it must be a column, build it in Power Query rather than DAX wherever you can — the reasons are in section 03.
Concept
Three ways to add a column
| Route | Where | Best for |
|---|---|---|
| Conditional Column | Add Column › Conditional Column | Banding and simple categorisation. No M required, and it is readable in the dialog |
| Custom Column | Add Column › Custom Column | Anything the dialog cannot express — arithmetic, text manipulation, nested logic |
| Index Column | Add Column › Index Column | Row numbers, for ranking or as a last-resort surrogate key |
| Column From Examples | Add Column › Column From Examples | Covered in Lesson 4 — best used to learn the M you needed |
The Conditional Column dialog
It builds an if/then/else chain visually. Conditions are evaluated top to bottom and the first match wins — which is the single most important thing to understand about it.
if Discount >= 0 then "None"else if Discount >= 0.10 then "Light"else "Heavy"Every row matches the first condition, so everything is labelled "None". The query does not error. The column looks populated. Every discount analysis built on it is wrong.
For a descending band, order conditions from most restrictive to least: check
>= 0.20 first, then >= 0.10, then the else.What the dialog cannot do
- Reference more than one column in a single condition (in older versions — recent ones allow column-to-column comparison).
- Perform arithmetic inside a condition.
- Handle nulls gracefully — a null compared with a number returns null, not false.
When you hit one of those, switch to a Custom Column. The dialog generates M anyway, so there is no penalty for moving.
Custom Columns and M basics
Add Column › Custom Column opens a formula box. A few things worth knowing:
| Concept | Syntax | Note |
|---|---|---|
| Reference a column | [ColumnName] | Square brackets, case-sensitive |
| Conditional | if … then … else … | else is mandatory — unlike Excel's IF |
| Null-coalesce | [Col] ?? 0 | Returns 0 if the column is null |
| Text concatenation | & | Both sides must be text |
| Convert to text | Text.From([Num]) | Concatenating a number without this errors |
| Error handling | try … otherwise … | Returns the fallback if the expression errors |
| Case sensitivity | — | M is case-sensitive. Text.Upper works; text.upper does not |
if [Discount] >= 0.10 then "Heavy" else "Light" looks safe. If [Discount] is null, the comparison returns null — not false — and the whole expression returns null.Write
if [Discount] = null then "Unknown" else if [Discount] >= 0.10 …, or use ([Discount] ?? 0). Handle null explicitly, first, every time.Index columns
Add Column › Index Column, from 0 or from 1. Genuinely useful for a few things:
- Preserving the source's row order, which Power Query does not otherwise guarantee.
- Creating a surrogate key when no natural key exists.
- Ranking, when combined with a sort.
The decision that actually matters
Column or measure?
| Column | Measure | |
|---|---|---|
| Calculated | Once, at refresh, per row | At query time, for the current filter context |
| Stored | Yes — costs memory | No |
| Can slice/group by it | Yes | No |
| Responds to filters | No — the value is fixed | Yes |
| Use for | Categories, bands, flags, attributes | Aggregations, ratios, anything summed |
The test: "do I want this on an axis or in a slicer?" If yes, column. If you want to total it, measure.
Power Query column or DAX calculated column?
If it must be a column, prefer Power Query. Four reasons:
| Aspect | Power Query column | DAX calculated column |
|---|---|---|
| Compression | Better — compressed like an imported column | Worse — computed after compression |
| Query folding | May fold to the source | Never — it happens in the model |
| Refresh cost | Computed during load | Recomputed after load, adding time |
| Where the logic lives | With the data preparation, visible in Applied Steps | In the model, harder to find |
The exception: a calculated column that must reference another table through a relationship — using RELATED — cannot be done in Power Query without a merge. Sometimes the DAX is genuinely simpler, and that is a fair reason.
Visual Explanation
The same requirement solved three ways, so you can see what each costs.
| Approach | Where | Storage | Sliceable | Verdict |
|---|---|---|---|---|
| Conditional Column in Power Query | Applied Steps | Compressed with the table | Yes | Best. Cheap, sliceable, logic sits with the cleaning |
| DAX calculated column | The model | Stored, less compressible | Yes | Works. Slightly worse on size and refresh |
| DAX measure with SWITCH | The model | None | No | Wrong tool. Cannot be put on an axis or in a slicer |
The third row is not a matter of taste — a measure genuinely cannot be used as a slicer field. If you build the banding as a measure and then try to slice by it, you will discover this the hard way.
Including it deliberately here is the point: the mechanics being easy does not make the choice right.
Power BI Demonstration
Discount Band. Add clauses in this order: Discount >= 0.20 → Heavy; >= 0.10 → Moderate; > 0 → Light; else None.> 0 clause to the top and refresh the preview. Everything discounted becomes "Light". Move it back.if … then … else if …. This is a useful way to learn the syntax.else is mandatory in M.if [Discount] = null then "Unknown" else if …. Test it by replacing one discount value with null.([Discount] ?? 0) inside the comparisons. Shorter, and it treats null as zero, which may or may not be what you want — decide deliberately.[Quantity] * [UnitPrice]. Set the type explicitly in the dialog's type dropdown rather than adding a separate type step.try Number.From([SomeTextColumn]) otherwise null on a column with mixed content. Note how errors become nulls rather than failing the refresh.Example Dataset
The Discount column here is real and varied — a good candidate for banding practice.
1.3 MB · 15067 rows · 13 columns · CSV
Columns & data types
| Column | Data type | Meaning |
|---|---|---|
OrderID | Text | Order identifier. One order can have several rows — one per product line. |
OrderDate | Date | Date the order was placed. Use this for time intelligence. |
ShipDate | Date | Date the order shipped. A second date column, for the role-playing dimension lesson. |
CustomerKey | Text | Foreign key to bwb-customers.csv. |
ProductKey | Text | Foreign key to bwb-products.csv. |
StoreKey | Text | Foreign key to bwb-stores.csv. Blank for non-store channels. |
Channel | Text | Online, Retail Store or Partner. |
Quantity | Whole number | Units sold on this line. |
UnitPrice | Decimal | List price per unit before discount. |
Discount | Decimal | Discount rate applied, 0 to 0.30. |
NetSales | Decimal | Revenue after discount. The measure you will sum most often. |
COGS | Decimal | Cost of goods sold for this line. |
Profit | Decimal | NetSales minus COGS. Pre-computed so you can validate your own DAX. |
Questions this dataset can answer
- Which category generates the most revenue, and is it also the most profitable?
- How did monthly revenue trend across the two years, and where is the seasonal peak?
- Which region has the highest revenue per customer?
- What share of revenue comes from discounted orders, and does discounting improve profit?
- Which products account for the top 20% of revenue?
Step-by-Step Exercise
Is Profitable = ([Profit] ?? 0) > 0. Type it as True/False.Expected Result
| Check | Expected |
|---|---|
| Discount Band distribution | The large majority in 'None' — most orders carry no discount |
| Order Size Band ordering | Verify by checking a known large order lands in Very Large |
| Is Profitable | Nearly all TRUE; the exceptions are heavily discounted lines |
| try … otherwise result | Bad values become null; the refresh succeeds |
| Index column position | Last in Applied Steps |
| Column audit | Discount Band and Order Size Band stay as columns; anything you would sum becomes a measure |
Gross Sales or Margin Amount column, both should go. They are numbers you would total, so they belong in measures: Gross Sales = SUMX(Sales, Sales[Quantity] * Sales[UnitPrice]) costs no storage and responds correctly to any filter.Discount Band and Order Size Band stay, because you want them on axes and in slicers. That is the whole distinction.Common Mistakes
Fix: Order from most restrictive to least. Then test with a known value from each intended band.
Fix: Test for null explicitly as the first condition, or use
?? to coalesce — and decide deliberately what null should mean.Fix: If you would sum it, make it a measure. Columns are for things you slice by.
Fix: Add index columns as the last step. Confirm with View Native Query.
Fix: Default to Power Query for columns. Use a DAX calculated column when it genuinely needs
RELATED or another model-level function.Professional Tip
Yes → it must be a column. Build it in Power Query.
No, I want to total it → it must be a measure. Do not build a column at all.
No, it is just an intermediate step → build it, use it, then remove it before loading.
That third case is worth naming. It is entirely reasonable to add a helper column, use it in the next step, and then remove it. A column that exists only to serve another step should not reach the model — it is pure storage cost with no analytical value.
Two more:
- Name bands so they sort correctly, or give them a sort column. "Heavy", "Light", "Moderate", "None" sorts alphabetically into nonsense. Either prefix them (
1 None,2 Light) or add a numeric band-order column and set Sort by column in the model. - Set the type in the Custom Column dialog rather than adding a separate Changed Type step. Shorter Applied Steps list, same result.
Knowledge Check
Knowledge Check
5 questions. Answer each one to see why the right answer is right.
Your Discount Band conditional column labels every row "None". What is the most likely cause?
In M, what does if [Discount] >= 0.10 then "Heavy" else "Light" return when Discount is null?
You want to let users slice a report by order size band. Should this be a column or a measure?
Why prefer a Power Query column over a DAX calculated column when both would work?
Where should an index column go in a query against a SQL database?
Practical Challenge
Audit and redesign a column-heavy query
You inherit a Power Query query that adds nine columns to a 12-million-row fact table. The model is 2.1 GB and refresh takes 25 minutes. Audit the columns, decide what each should actually be, and produce a redesign.
- For each of the nine columns, decide: keep as a Power Query column, convert to a measure, remove entirely, or move
- Justify each decision in one sentence
- Write the DAX for any measure you propose
- Identify the column causing the largest storage cost and explain why
- Identify the step causing the refresh time problem
- Estimate the effect of your redesign
1 Gross Sales = [Quantity] * [UnitPrice]
2 Margin Amount = [NetSales] - [COGS]
3 Margin Percent = [Margin Amount] / [NetSales]
4 Discount Band = conditional: None / Light / Moderate / Heavy
5 Order Size Band = conditional: Small / Medium / Large / Very Large
6 Is Discounted = [Discount] > 0
7 Order Year Month = Text.From(Date.Year([OrderDate])) & "-" &
Text.PadStart(Text.From(Date.Month([OrderDate])), 2, "0")
8 Row ID = Index column, added as step 3 of 14
9 Full Timestamp = [OrderDate] & " " & [OrderTime] (Date/Time, to the second)Apply the axis question to each one: would you put it on an axis or in a slicer, or would you sum it?
For the storage question, think about cardinality — how many distinct values does each column contain? VertiPaq compresses by distinct value, so the column with the most distinct values costs the most.
For refresh time, one of the nine is in the wrong place in the query and is doing enormous damage.
Solution
Try the challenge properly before opening this. Reading a solution you have not attempted feels like learning, but it is not.
The audit
| Column | Verdict | Why | |
|---|---|---|---|
| 1 | Gross Sales | → Measure | A number you sum. Costs 12M values of storage for something DAX computes free |
| 2 | Margin Amount | → Measure | Same — summable |
| 3 | Margin Percent | → Measure | Doubly wrong. A ratio stored per row cannot be aggregated correctly — averaging it gives the wrong answer, as Module 0 Lesson 4 covered |
| 4 | Discount Band | Keep | Sliced by. Low cardinality (4 values), so it compresses to almost nothing |
| 5 | Order Size Band | Keep | Same reasoning |
| 6 | Is Discounted | Keep, but redundant | It is derivable from Discount Band ≠ None. Cheap (boolean), so harmless — but I would drop it for tidiness |
| 7 | Order Year Month | → Remove | This belongs in the date dimension, not the 12M-row fact. Storing it here repeats 24 distinct values 12 million times |
| 8 | Row ID | → Remove, or move to last | 12M distinct values — worst possible compression. And at step 3 it breaks folding |
| 9 | Full Timestamp | → Remove or split | The single biggest storage cost. See below |
The largest storage cost
VertiPaq compresses by building a dictionary of distinct values per column. A column with millions of distinct values gets almost no compression — it is close to storing the raw value for every row.
Row ID is the close second for exactly the same reason: an index column is unique by construction, so it has one distinct value per row and cannot compress at all.
The fix: split Full Timestamp into a
Date column (731 distinct values) and, only if the time is genuinely needed, a Time column rounded to the minute or hour (1,440 or 24 distinct values). Two low-cardinality columns compress dramatically better than one high-cardinality one.The refresh time problem
The index column at step 3 of 14. It breaks query folding, so the eleven steps after it — including any filtering and column removal — run locally on all 12 million rows instead of being pushed to the database.
Moving it to the end, or removing it entirely, is likely to be the single largest improvement to the 25-minute refresh.
The measures
Gross Sales =
SUMX ( Sales, Sales[Quantity] * Sales[UnitPrice] )
Total Margin =
SUM ( Sales[NetSales] ) - SUM ( Sales[COGS] )
-- the important one: a ratio must divide two aggregates,
-- never average a stored per-row percentage
Margin % =
DIVIDE (
[Total Margin],
SUM ( Sales[NetSales] )
)Gross Sales and Margin Amount as columns are merely wasteful — summing them gives the right answer, it just costs storage unnecessarily.Margin Percent as a column is wrong. Any visual aggregating it will average the per-row percentages, giving every order line equal weight regardless of size. A £10 line and a £10,000 line count the same. The reported margin will be materially incorrect and will look entirely plausible.This is the non-additive ratio problem from Module 0 Lesson 4, and it is the most consequential finding in the audit.
Estimated effect
| Change | Effect |
|---|---|
| Remove Full Timestamp, add Date + rounded Time | Largest single reduction in model size |
| Remove Row ID | Removes a 12M-distinct-value column, and restores folding |
| Move remaining index (if kept) to last | Restores folding for 11 steps |
| Remove Order Year Month to the date dimension | Removes a 12M-row column holding 24 distinct values |
| Convert columns 1–3 to measures | Removes three 12M-row numeric columns |
| Keep bands 4 and 5 | Negligible cost, real analytical value |
I would expect a substantial reduction in both model size and refresh time — but I would measure rather than promise a figure. Run VertiPaq Analyzer before and after (Module 11 Lesson 2) and report the actual numbers.
The three findings that matter most
1. Margin Percent is not just wasteful, it is wrong. The other findings cost money; this one costs correctness, and it is the kind of error that survives for years because the number always looks reasonable.
2. High-cardinality columns are where model size actually lives. Nine added columns sounds like the problem is quantity. It is not — two of them account for most of the cost, and both for the same reason.
3. One misplaced step explains the refresh time. Not the volume of transformations, but where one of them sits. That is a much cheaper fix than rewriting the query, and you would never find it without checking the fold.
Lesson Summary
- Conditional Column for banding, Custom Column for anything the dialog cannot express, Index Column for row numbers.
- Conditional clauses are evaluated top to bottom, first match wins. Order most restrictive first, or everything lands in the first band.
- In M, comparing null with a number returns null, not false. Handle null explicitly or coalesce with
??. - The axis question: if you slice by it, it must be a column. If you sum it, it should be a measure.
- A ratio stored as a column is wrong, not merely wasteful — it averages incorrectly at every level.
- Prefer Power Query columns over DAX calculated columns: better compression, possible folding, no post-load cost.
- Index columns break folding — add them last.
- High-cardinality columns dominate model size. Split Date/Time; remove unique identifiers you do not need.
Syllabus topics delivered
Progress
Marking a lesson complete stores your progress in this browser and updates your BitWithBite learner dashboard. Nothing is uploaded anywhere.