Creating fields
Formula adds new fields or overwrites existing ones. One tool can hold several expressions, evaluated top to bottom — so a later expression can use a field created by an earlier one in the same tool.
[Revenue] - [Cost]
[Price] * [Quantity]
[First Name] + " " + [Last Name]Field names go in square brackets; text literals in double quotes.
Conditional logic
IF [Revenue] > 1000 THEN "High"
ELSEIF [Revenue] > 100 THEN "Medium"
ELSE "Low"
ENDIFOr the compact form for a two-way choice:
IIF([Revenue] > 1000, "High", "Low")A Switch is available where you are matching one field against many discrete values.
Useful functions
| Category | Examples |
|---|---|
| String | Trim, Uppercase, Left, Right, Substring, Replace, Contains |
| Numeric | Round, Abs, Ceil, Floor, Mod |
| Date | DateTimeNow, DateTimeAdd, DateTimeDiff, DateTimeFormat |
| Null | IsNull, IsEmpty, Null() |
| Conversion | ToNumber, ToString, DateTimeParse |
Nulls
[Revenue] - [Discount] returns null for every row where Discount is null — quietly wiping out values that looked fine.
[Revenue] - IIF(IsNull([Discount]), 0, [Discount])The Data Cleansing tool can replace nulls across many fields at once, which is usually tidier than doing it per formula.
Multi-Field Formula
When the same expression applies to many fields — trimming every text column, say — use Multi-Field Formula rather than adding twenty expressions. It applies one formula across a selected set, with [_CurrentField_] standing in for each.