8. Adding Columns: Power Query Add Column Tab and DAX Calculated Columns
8.3 Custom Column
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 ??.