Column types in Mammoth: IDs, currency and percentages
Every column is stored as Text, Number or Date. A Number or Text column can also carry a meaning on top of that, called its subtype: it is an ID, a currency amount or a percentage. You change a column's type from Column Manager, a Convert Column Type task or an Add Column task. The subtype never changes stored values; it changes the header tag, which number formats you can change, and the warnings you see.
Each column type and what it means
| Type | Stored as | Header tag | What it means |
|---|---|---|---|
| Text | Text | Aa | Free text or labels. |
| ID (under Text) | Text | key icon | A value that identifies a row, like a customer or order ID. Kept as text, so leading zeros stay. |
| Number | Number | 123 | Numeric values you can calculate with. |
| ID (under Number) | Number | key icon | A number that identifies a row. Isn't summed or averaged by default; adding or averaging it has no meaning. |
| Currency | Number | $ icon | An amount of money. |
| Percentage | Number | % icon | A share or rate. |
| Date | Date | calendar icon | A calendar date or timestamp. |
Date columns have no subtypes. A subtype doesn't change what a saved task calculates.
Read the type tag in a column header
Each header in the data grid starts with a small grey tag showing the column's type. Hover it for its meaning, for example "ID (Number). A number that identifies a row, like a customer or order ID. Isn't summed or averaged."
The tag is a label only. To change the type, use one of the places below.
How Mammoth detects IDs, currency and percentages
When data loads, Mammoth looks for IDs, currency amounts and percentages. It sets a subtype only when it is confident, and only one that fits the column's storage type. In the type menu, the entry Mammoth chose is marked detected.
A type you choose always wins. Choosing Number or Text marks a column as plain, and detection doesn't change it back.
Change a column's type or meaning
The type menu is the same everywhere. Its first level is Number, Text and Date:
- Number opens Number, ID, Percentage and Currency.
- Text opens Text and ID.
- Date is a single option.
While a change is pending, the menu head shows Reset to put back the type the column had.
| Where you change it | What the change affects |
|---|---|
| Column Manager in the data editor (click the column count in the bottom bar, then Column Manager) | Choose This view only to change only this View, or apply it to the whole dataset ("Modifies the dataset schema for all views."). |
| Column Manager in the dataset's settings | The dataset, so every View sees the new type. |
| Convert Column Type task | This View, from that task onward. Picking another entry under the column's own type (for example Percentage for a Number column) changes only the meaning, not the values. See Convert text to dates and numbers. |
| Add Column task | The new column gets the type you pick. See Add, split, copy and delete columns. |
If a subtype can't be saved, you see "Couldn't change what this column means".
Why some number format buttons are locked
A Currency, Percentage or ID column decides part of its number format. In the column menu's Formatting section, those buttons are greyed out with the note "Set by the column's type" below them.
| Type | Controls you can't change in the column menu |
|---|---|
| Currency | Percentage. If the type was set for the whole dataset, the currency symbol too. |
| Percentage | Currency. If the type was set for the whole dataset, the percentage button too. |
| ID (Number) | Thousands separator, so codes show as typed. |
| Number, set as plain for the whole dataset | Currency and percentage. |
A type that Mammoth only detected locks less. A detected Currency greys out only the percentage button, a detected Percentage only the currency picker, and a detected ID nothing. Decimal places are always yours to change. See Format a number column as currency, percent or decimals.
Warnings when you sum or average an ID or a percentage
Summing or averaging an ID adds up codes, and summing percentages rarely means anything. Mammoth warns you where you pick an aggregation:
| Where | ID column with Sum or Average | Percentage column with Sum |
|---|---|---|
| Explore card Show values by | "Adding or averaging it adds up the codes. Did you mean Count distinct?" | "Adding percentages rarely means anything. Did you mean Average?" |
| Group & Pivot task | Same text, with Swap to Count distinct and Keep | Same text, with Swap to Average and Keep |
| Create Metric from a column header | "Sum adds up the codes." or "Average of the codes is not a quantity." | "Adding percentages rarely means anything. Average usually does." |
Each warning starts with the column name, for example "order_id is an ID." The warnings don't block you. In Group & Pivot, Keep dismisses the warning for that aggregation and keeps your choice.
When you pick a column for Sum or Average in an Explore card, ID columns are listed last with the hint "it's an ID".
Every warning a column's meaning can trigger
Some functions use a column's meaning to question a choice that is probably a mistake. Most warnings start with a bold lead such as "order_id is an ID." None of them blocks Apply.
| Function | Warning | When |
|---|---|---|
| Math Function | "IDs are labels, not amounts, so doing arithmetic on them isn't meaningful." | A formula (in Formula mode) uses an ID column. |
| Math Function | "This formula mixes an amount of money with a percentage. Check that the result is what you mean." | A formula (in Formula mode) uses a Currency and a Percentage column together. |
| Window Function | "IDs are labels, not amounts, so a running total or ranking of them isn't meaningful." | A sum, average or ranking runs over an ID column. |
| Group & Pivot | "Adding or averaging it adds up the codes. Did you mean Count distinct?" | Sum or Average of an ID column. Offers Swap to Count distinct or Keep. |
| Group & Pivot | "Adding percentages rarely means anything. Did you mean Average?" | Sum of a Percentage column. Offers Swap to Average or Keep. |
| Join, Lookup | Key type and meaning warnings | The keys are stored as different types, one is an ID and the other isn't, or they are in different currencies. See Join two datasets on a shared column. |
| Unpivot | "The new value column does not carry over the meaning of the columns you stack (an ID, a currency or a percentage). It starts as a plain column." | A stacked column has a meaning. |
Data Check also suggests checks for ID, Percentage and Currency columns.
Troubleshooting column types
| Symptom | Cause | Fix |
|---|---|---|
| "Couldn't change what this column means" | The subtype couldn't be saved | Try again from Column Manager or a Convert Column Type task |
| A number format button is greyed out with "Set by the column's type" | The column's type decides that setting | Change the column's type |
| Leading zeros disappeared from a code | The code was converted to a Number | Use ID under Text to keep leading zeros |
| Detection keeps a column plain | You chose Number or Text, which wins over detection | Pick the subtype you want from the type menu |
Related
- Convert text to dates and numbers: the Convert Column Type task and its format codes.
- Rename, format and sort columns: number and date display formats.
- Group, pivot and unpivot: where the Sum and Average warnings appear.
- Calculate with formulas: Math Function and Window Function warnings.
- Metrics: Create Metric from a column header.
Something wrong? support@mammoth.io