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
Mitrano, ithe chuk karu naka: 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, karan null - 5 returns null. Set the type in the dialog, and use ([Discount] ?? 0) when a value may be null.
Practice task
Create Order Line Total = Amount + Delivery Fee − Discount, handling null Discount with ??.