8. Adding Columns: Power Query Add Column Tab and DAX Calculated Columns
8.3 Custom Column
In short: A Custom Column is an M formula evaluated row by row.
Start with a problem
Rani wants Net Amount = Amount − Discount, but only when the order is Delivered; cancelled orders should be 0.
Explain simply
A Custom Column is an M formula evaluated row by row. Column names go in square brackets; text in double quotes.
Show the data
| Order ID | City | Amount | Discount | Delivery Time Mins | Order Status |
|---|---|---|---|---|---|
| 201 | Pune | 500 | 50 | 8 | Delivered |
| 202 | Nashik | 300 | 0 | 12 | Delivered |
| 203 | Pune | 700 | 100 | 9 | Delivered |
| 204 | Kolhapur | 400 | 0 | 18 | Delivered |
| 205 | Nashik | 0 | 0 | null | Cancelled |
About this data: This table uses columns: Order ID, City, Amount, Discount, Delivery Time Mins, Order Status. This table uses columns: Order Status, Orders, Net Amount (₹). This table uses columns: Wrong result, Reason, Correction. Values shown are the lesson examples already on this page (not a new dataset).
Fictional sample data. Practice file: column_vs_measure_orders.csv (source: src/practice/).
Demonstrate
- Add Column › Custom Column.
-
Name
Net Amount; formulaif [Order Status] = "Delivered" then [Amount] - [Discount] else 0› OK.Your result should look like this:
Net Amount 450, 300, 600, 400, 0 -
Set type Fixed decimal number (Custom Column defaults to Any).
Show the result
Your result should look like this (fictional sample):
| Order Status | Orders | Net Amount (₹) |
|---|---|---|
| Delivered | 4 | 1,750 |
| Cancelled | 1 | 0 |
Explain what happened
M evaluated the formula once per row; the if expression returned 0 for order 205.
Explore further
- M is case-sensitive: [order status] fails.
Detailed reference for this lesson:
Write your own M formula for each row.
Steps in Power BI
- Add Column › Custom Column.
- New column name:
Net Amount. - Custom column formula: double-click fields in Available columns to insert them, e.g.
[Amount] - [Discount]. - Make sure the message at the bottom says No syntax errors have been detected › OK.
- Set the data type (the column shows ABC123 = "any" until you do): click the type icon in the header › Fixed Decimal Number.
= Table.AddColumn(Source, "Net Amount", each [Amount] - [Discount], Currency.Type)
// more examples
each [Amount] + [Delivery Fee] // Order Line Total
each if [Payment Mode] = "Cash on Delivery" then "Cash" else "Digital"
each Text.Upper(Text.Start([City], 3)) & "-" & [Area] // "PUN-Kothrud"
each Date.DayOfWeekName([Order Date], "en-IN") // "Monday"
Ravindra Bagale's Tip
Friends, don't make a mistake here: M is case-sensitive: text.upper or [amount] gives an error. The other two traps are forgetting to set the column type (so it loads as "any") and null arithmetic, because null - 5 returns null. Set the type in the dialog, and use ([Discount] ?? 0) when a value may be null.
Ravindra Bagale's Tip – मराठी
मित्रांनो, इथे चूक करू नका: M case-sensitive आहे: text.upper किंवा [amount] error देतं. इतर दोन सापळे म्हणजे column चा type set करायला विसरणं (मग तो "any" म्हणून load होतो) आणि null चं गणित, कारण null - 5 null देतं. Dialog मध्येच type set करा, आणि value null असू शकत असेल तर ([Discount] ?? 0) वापरा.
Ravindra Bagale's Tip – हिंदी
दोस्तों, यहाँ गलती मत करना: M case-sensitive है: text.upper या [amount] error देता है. बाकी दो जाल हैं column का type set करना भूल जाना (तो वह "any" के रूप में load होता है) और null का हिसाब, क्योंकि null - 5 null देता है. Dialog में ही type set करो, और value null हो सकती हो तो ([Discount] ?? 0) इस्तेमाल करो.
Practice task
Create Order Line Total = Amount + Delivery Fee − Discount, handling null Discount with ??.
Fix common mistakes
| Wrong result | Reason | Correction |
|---|---|---|
| Column type ABC123 (Any) | No type set | Set type |
| Expression.Error: field 'order status' not found | Wrong case | Match the exact column name |
| Error: cannot apply operator - to text | Amount still text | Fix types first |
Practise independently
Practice task
Write a custom column Discount % = Discount / Amount, safe for Amount 0.
Hints
- Use if.
Solution
if [Amount] = 0 then null else [Discount] / [Amount] → 201: 10%, 203: 14.3%, others 0% / null.
Think like a professional
Row-level logic in Power Query is fine; ratios for reports belong in DAX measures.
Revision card
Interview card
Comparison card