Home Course home Curriculum Learner dashboard Skill tree Business projects Datasets Final project
🧹 Module 3 · Power Query & Data Transformation Intermediate LESSON 20 / 72

Adding Conditional & Custom Columns

30 min 5 quiz questions 1 practical challenge 5 syllabus topics

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?

Lesson 20 of 72 · Power Query & Data Transformation 28%
01

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.

By the end of this lesson you will be able to
  • 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
02

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.

The rule this lesson builds to
If you want to slice, group or filter by it, it must be a column. Measures cannot go on an axis or in 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.
03

Concept

Three ways to add a column

The routes
RouteWhereBest for
Conditional ColumnAdd Column › Conditional ColumnBanding and simple categorisation. No M required, and it is readable in the dialog
Custom ColumnAdd Column › Custom ColumnAnything the dialog cannot express — arithmetic, text manipulation, nested logic
Index ColumnAdd Column › Index ColumnRow numbers, for ranking or as a last-resort surrogate key
Column From ExamplesAdd Column › Column From ExamplesCovered 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.

Order matters, and getting it wrong is silent
Banding a discount rate with these rules, in this order:

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:

M essentials for custom columns
ConceptSyntaxNote
Reference a column[ColumnName]Square brackets, case-sensitive
Conditionalif … then … else …else is mandatory — unlike Excel's IF
Null-coalesce[Col] ?? 0Returns 0 if the column is null
Text concatenation&Both sides must be text
Convert to textText.From([Num])Concatenating a number without this errors
Error handlingtry … otherwise …Returns the fallback if the expression errors
Case sensitivity—M is case-sensitive. Text.Upper works; text.upper does not
The null trap in M
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.
Index columns break query folding
Covered in Module 2 Lesson 3 and worth repeating: an index column ends folding for every step below it. Add index columns last, after all filtering and column reduction, or you will pull the entire source table across the network.

The decision that actually matters

Column or measure?

ColumnMeasure
CalculatedOnce, at refresh, per rowAt query time, for the current filter context
StoredYes — costs memoryNo
Can slice/group by itYesNo
Responds to filtersNo — the value is fixedYes
Use forCategories, bands, flags, attributesAggregations, 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:

AspectPower Query columnDAX calculated column
CompressionBetter — compressed like an imported columnWorse — computed after compression
Query foldingMay fold to the sourceNever — it happens in the model
Refresh costComputed during loadRecomputed after load, adding time
Where the logic livesWith the data preparation, visible in Applied StepsIn 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.

04

Visual Explanation

The same requirement solved three ways, so you can see what each costs.

"Band the discount rate" — three implementations
ApproachWhereStorageSliceableVerdict
Conditional Column in Power QueryApplied StepsCompressed with the tableYesBest. Cheap, sliceable, logic sits with the cleaning
DAX calculated columnThe modelStored, less compressibleYesWorks. Slightly worse on size and refresh
DAX measure with SWITCHThe modelNoneNoWrong 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.

Notice the Gross Sales step is flagged as questionable
It is a perfectly good demonstration of a Custom Column, and it is the wrong design. A number you intend to sum belongs in a measure — it costs no storage, it responds to filter context, and it cannot drift out of step with the raw columns.

Including it deliberately here is the point: the mechanics being easy does not make the choice right.
05

Power BI Demonstration

Build a conditional column
Add Column › Conditional Column. Name it Discount Band. Add clauses in this order: Discount >= 0.20 → Heavy; >= 0.10 → Moderate; > 0 → Light; else None.
Prove the ordering trap
Move the > 0 clause to the top and refresh the preview. Everything discounted becomes "Light". Move it back.
Read the generated M
Click the step and look at the formula bar. The dialog produced a nested if … then … else if …. This is a useful way to learn the syntax.
Convert it to a Custom Column
Create a Custom Column with the same logic written by hand. Note that else is mandatory in M.
Add null handling
Change your expression to if [Discount] = null then "Unknown" else if …. Test it by replacing one discount value with null.
Try the ?? operator
Rewrite as ([Discount] ?? 0) inside the comparisons. Shorter, and it treats null as zero, which may or may not be what you want — decide deliberately.
Write an arithmetic custom column
[Quantity] * [UnitPrice]. Set the type explicitly in the dialog's type dropdown rather than adding a separate type step.
Use try … otherwise
Write try Number.From([SomeTextColumn]) otherwise null on a column with mixed content. Note how errors become nulls rather than failing the refresh.
Add an index column in the wrong place
On a database source, add one near the top. Right-click a step below it → View Native Query and confirm folding has stopped. Then move it to the end and confirm folding returns.
Decide what should be a measure
Look at every column you added. For each, ask: would I put this on an axis, or would I sum it? Delete any that should be measures.
Add ColumnConditional Column
Set the data type in the Custom Column dialog
The dialog has a type dropdown. Using it avoids a separate Changed Type step and keeps your Applied Steps list shorter and more readable. Small habit, compounding benefit.
06

Example Dataset

The Discount column here is real and varied — a good candidate for banding practice.

bwb-retail-sales.csv
1.3 MB · 15067 rows · 13 columns · CSV
Business context
Two full years (Jan 2024 – Dec 2025) of order-line transactions for a mid-sized multi-channel retailer selling across six product categories, five regions and three channels. Contains real seasonality — a November peak, a February trough — and category margins that differ enough to make the analysis interesting. This is the course's primary fact table.

Columns & data types

ColumnData typeMeaning
OrderIDTextOrder identifier. One order can have several rows — one per product line.
OrderDateDateDate the order was placed. Use this for time intelligence.
ShipDateDateDate the order shipped. A second date column, for the role-playing dimension lesson.
CustomerKeyTextForeign key to bwb-customers.csv.
ProductKeyTextForeign key to bwb-products.csv.
StoreKeyTextForeign key to bwb-stores.csv. Blank for non-store channels.
ChannelTextOnline, Retail Store or Partner.
QuantityWhole numberUnits sold on this line.
UnitPriceDecimalList price per unit before discount.
DiscountDecimalDiscount rate applied, 0 to 0.30.
NetSalesDecimalRevenue after discount. The measure you will sum most often.
COGSDecimalCost of goods sold for this line.
ProfitDecimalNetSales 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?
07

Step-by-Step Exercise

Load the sales file and reduce it
Keep OrderID, OrderDate, ProductKey, Channel, Quantity, UnitPrice, Discount, NetSales, Profit.
Build a Discount Band conditional column
Four bands. Verify the distribution by grouping on it — you should see the majority in None.
Build an Order Size Band
Based on NetSales: under 50 = Small, 50–250 = Medium, 250–1000 = Large, above = Very Large. Watch the ordering.
Build a null-safe margin flag with a Custom Column
Is Profitable = ([Profit] ?? 0) > 0. Type it as True/False.
Write a try … otherwise column
Convert a text column to a number safely, so bad values become null instead of failing.
Add an index column at the end
And confirm it is the last step in Applied Steps.
Audit your columns
For every column you added, write down: is this something I would slice by, or something I would sum? Delete anything in the second category and note what measure you would write instead.
08

Expected Result

Expected results
CheckExpected
Discount Band distributionThe large majority in 'None' — most orders carry no discount
Order Size Band orderingVerify by checking a known large order lands in Very Large
Is ProfitableNearly all TRUE; the exceptions are heavily discounted lines
try … otherwise resultBad values become null; the refresh succeeds
Index column positionLast in Applied Steps
Column auditDiscount Band and Order Size Band stay as columns; anything you would sum becomes a measure
What the audit should have caught
If you built a 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.
09

Common Mistakes

Ordering conditional clauses least-restrictive first
The first matching condition wins, so a broad condition at the top captures everything below it. The column populates fully and is entirely wrong.

Fix: Order from most restrictive to least. Then test with a known value from each intended band.
Not handling null in a comparison
In M, comparing null with a number returns null, not false. The whole expression returns null and those rows silently disappear from any filter on the column.

Fix: Test for null explicitly as the first condition, or use ?? to coalesce — and decide deliberately what null should mean.
Building a summable number as a column
A Gross Sales or Margin column costs storage, does not respond to filter context, and can drift out of step with the columns it was derived from.

Fix: If you would sum it, make it a measure. Columns are for things you slice by.
Adding an index column early in a database query
Folding stops there, so every step below it runs locally on the full table.

Fix: Add index columns as the last step. Confirm with View Native Query.
Writing calculated columns in DAX when Power Query would do
Worse compression, no folding, extra refresh time, and the logic ends up somewhere less discoverable than Applied Steps.

Fix: Default to Power Query for columns. Use a DAX calculated column when it genuinely needs RELATED or another model-level function.
10

Professional Tip

Ask the axis question before writing anything
Before adding any column, answer one question: "Will this appear on an axis, in a slicer, or in a legend?"

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.
11

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?

12

Practical Challenge

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.

Requirements
  • 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
Reference · The nine added columns
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.

13

Solution

Try the challenge properly before opening this. Reading a solution you have not attempted feels like learning, but it is not.

The audit

ColumnVerdictWhy
1Gross Sales→ MeasureA number you sum. Costs 12M values of storage for something DAX computes free
2Margin Amount→ MeasureSame — summable
3Margin Percent→ MeasureDoubly wrong. A ratio stored per row cannot be aggregated correctly — averaging it gives the wrong answer, as Module 0 Lesson 4 covered
4Discount BandKeepSliced by. Low cardinality (4 values), so it compresses to almost nothing
5Order Size BandKeepSame reasoning
6Is DiscountedKeep, but redundantIt is derivable from Discount Band ≠ None. Cheap (boolean), so harmless — but I would drop it for tidiness
7Order Year Month→ RemoveThis belongs in the date dimension, not the 12M-row fact. Storing it here repeats 24 distinct values 12 million times
8Row ID→ Remove, or move to last12M distinct values — worst possible compression. And at step 3 it breaks folding
9Full Timestamp→ Remove or splitThe single biggest storage cost. See below

The largest storage cost

Full Timestamp, by a wide margin
A Date/Time to the second across two years of trading has up to 63 million distinct values. Even limited by actual order times, it will run to millions.

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

DAX · Replacing columns 1, 2 and 3
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] )
)
Why column 3 was the worst of the three
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

ChangeEffect
Remove Full Timestamp, add Date + rounded TimeLargest single reduction in model size
Remove Row IDRemoves a 12M-distinct-value column, and restores folding
Move remaining index (if kept) to lastRestores folding for 11 steps
Remove Order Year Month to the date dimensionRemoves a 12M-row column holding 24 distinct values
Convert columns 1–3 to measuresRemoves three 12M-row numeric columns
Keep bands 4 and 5Negligible 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.

14

Lesson Summary

Key takeaways
  • 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

Adding columnsConditional columnsCustom columnsIndex columnsPower Query vs DAX for columns
15

Progress

Marking a lesson complete stores your progress in this browser and updates your BitWithBite learner dashboard. Nothing is uploaded anywhere.

Lesson complete — 28% of Power BI Mastery done
Progress saved. Keep the momentum: the next lesson builds directly on this one.