The Expression processor is often used for calculations, but it also includes very handy functions to clean, transform, and enrich your data before publishing. Here are a few practical examples you can reuse right away 👇
✏️ Text formatting
Use these functions to standardize casing, improve readability, and generate clean display fields (names, labels, titles…).
| Need | Function | Expression | Output |
|---|---|---|---|
| Uppercase | upper() | =upper("hello") | "HELLO" |
| Lowercase | lower() | =lower("HELLO") | "hello" |
| Capitalize first letter | capitalize() | =capitalize("hello world") | "Hello world" |
| Capitalize each word | capitalize_all() | =capitalize_all("hello world") | "Hello World" |
✂️ Extract / slice text
Perfect to rework structured identifiers (IDs, references…), grab prefixes/suffixes, or rebuild a required format.
| Need | Function | Expression | Output |
|---|---|---|---|
| Get the first N characters | left() | =left(postal_code, 2) | e.g. "75" from “75001” |
| Get the last N characters | right() | =right(reference, 4) | e.g. "C3D4" from “A1B2C3D4” |
| Extract from the middle (pos, length) | mid() | =mid(code, 2, 3) | e.g. "BCD" from “ABCD” |
| Left-pad with zeros | rjust() | =rjust("4400", 5, "0") | "04400" |
🔗 Create readable categories (ternary operator)
The ternary operator is great to turn raw values into clear business-friendly labels without changing the source data.
condition ? true_result : false_result
| Need | Expression | Output |
|---|---|---|
| Categorize cities | =population > 10000 ? "Big city" : "Small town" | "Big city" / "Small town" |
| Pass/fail label | =score >= 10 ? "Passed" : "Rejected" | "Passed" / "Rejected" |
📅 Flag old/recent records
A common use case: highlight old / recent records. E.g. events/incidents from the last 12 months.
| Need | Expression | Meaning |
|---|---|---|
| Flag old records | =datediff(my_date, now(), "day") > 365 | True → old records |
| Flag recent records | =datediff(my_date, now(), "day") <= 365 | True → recent records |
If you need a different granularity, datediff() also supports these units:
- Year : “year”
- Month : “month”
- Day : "day"
- Hour : "hour"
- Minute : "minute"
- Second : "second"
And you, what are your most-used Expression formulas to clean, categorize, or filter your data? Share your best tips in the comments.