2.3.1. Data Types and Column Transformations
💡 First Principle: A data type is a promise about what a column contains, and the engine uses that promise to store, compress, sort, and calculate. Choosing the right type early prevents errors, shrinks the model, and makes features like date hierarchies and numeric aggregation work correctly.
| Power Query type | Use for | Notes |
|---|---|---|
| Whole Number | Counts, integer keys, quantities | Smallest, fastest numeric storage |
| Decimal Number | Measurements, ratios | Floating point; tiny rounding differences possible |
| Fixed Decimal Number | Currency | Four decimal places, avoids floating-point rounding in financial sums |
| Percentage | Rates stored as fractions | Stored as decimal, formatted as % |
| Date / Date/Time / Time | Calendar values | Use Date when time isn't needed (lower cardinality) |
| Date/Time/Timezone, Duration | Offsets, elapsed time | Converted to Date/Time and Decimal in the model |
| Text | Names, codes, descriptions | Codes with leading zeros (ZIP 02134) must stay Text |
| True/False | Flags |
Change types via the header icon or Transform > Data Type. When source text uses a regional format, use Change Type > Using Locale so "03/04/2026" is read as 3 April rather than March 4. Set types deliberately rather than relying on the automatic Changed Type step, which hard-codes column names and guesses from sample rows.
Column-creation tools, in rough order of how often scenarios name them:
- Column From Examples: type a few desired outputs and Power Query infers the transformation (extracting a domain from email addresses, combining first and last names). Ideal when "minimize development effort" is the qualifier.
- Conditional Column: if-then-else rules through a dialog, such as "if Amount > 1000 then 'Large' else 'Standard'".
- Custom Column: an M expression for anything else, such as
[Quantity] * [UnitPrice]. - Index Column: sequential numbers from 0 or 1, used for surrogate keys and preserving order.
- Split Column: by delimiter, number of characters, positions, or case transitions, into columns or rows.
- Merge Columns: concatenate with a separator, for example to build a composite key.
- Extract and Format: text before/after a delimiter, first/last characters, length, trim, case.
Prefer transformations that reduce cardinality, such as splitting a Date/Time column into Date and Time or dropping the time entirely when it isn't needed, because they directly shrink the model (see 3.3.1).
⚠️ Exam Trap: Converting product codes or postal codes like "00742" to Whole Number strips the leading zeros permanently and breaks matches with other tables. Identifiers you never do arithmetic on belong in Text, even if they look numeric.
Reflection Question: Why is Fixed Decimal Number the preferred type for a currency column that's summed across millions of rows, when Decimal Number can also hold the same values?
