Master M's four primitive value types — number, text, logical, and null — and learn how arithmetic, string, and logical operations actually work at the language level. This foundation lesson eliminates the type errors and silent data corruption that frustrate every intermediate Power Query user.

You're building a Power Query transformation to clean up a sales export. The data comes in with prices stored as text, discount flags stored as 1 and 0 instead of true/false, and a handful of columns that look empty but aren't quite the same as "nothing." You write what seems like a perfectly reasonable formula — and Power Query throws a type error, or silently produces the wrong result, or behaves inconsistently depending on which row it's evaluating.
Sound familiar? These frustrations almost always trace back to the same root cause: not having a solid mental model of how M represents the most fundamental kinds of data. Before you can write elegant transformations, combine datasets, or build reusable functions, you need to understand the ground floor — the primitive value types that everything else is built on top of.
By the end of this lesson, you'll know exactly how M represents numbers, text, logical values, and null; how arithmetic, comparison, and string operations actually work at the language level; and why the difference between null and an empty string matters far more than it might seem. This isn't abstract theory — it's the practical foundation that makes every other M concept click into place.
What you'll learn:
null and why it propagates through expressionsYou should be comfortable opening the Power Query Editor and writing basic let…in expressions. If the structure of an M query looks unfamiliar, take a few minutes with M Language Fundamentals: Syntax, Types, and Expressions for Power Query first — that article covers the basic grammar this lesson builds on.
Before diving into each type, it helps to understand what M means by a value. In M, everything is a value — a number, a piece of text, a table, a function. Every value has a type, which describes what that value is and what you're allowed to do with it.
Primitive types are the simplest values: they carry data but have no internal structure you can drill into. A number like 42.5 is just a number. You can do math with it. You can't index into it or look up fields from it. That simplicity is what makes primitives the foundation of everything more complex.
M is a functional language, which means values are immutable — you never modify a value in place, you create new values from operations on existing ones. This matters because it explains why Text.Replace("hello", "l", "L") doesn't change "hello" — it returns a new text value.
Key insight
M doesn't have "variables" in the traditional sense. Every name in a let block is bound to a value, not a mutable container. Understanding this prevents a whole class of confusion when you try to "update" a step.
M has a single numeric type called number. Unlike many languages that distinguish integers from floats, M uses a double-precision 64-bit floating-point representation for all numbers. This means 1, 1.0, and 1.00 are all the same value.
let
wholeNumber = 42,
decimalNumber = 19.95,
negativeNumber = -7.5,
scientificForm = 1.5e3 // equivalent to 1500
in
wholeNumber
The fact that M has a single number type is both a convenience and a source of subtle bugs. Because everything is floating-point, division between two whole numbers doesn't automatically produce a whole number:
let
result = 7 / 2 // 3.5, not 3
in
result
If you need integer division, M provides Number.IntegerDivide(7, 2), which returns 3.
M supports the four standard arithmetic operators: +, -, *, /. There is no built-in modulo operator as a symbol — you use Number.Mod(dividend, divisor) instead.
let
unitPrice = 24.99,
quantity = 12,
discount = 0.10,
subtotal = unitPrice * quantity,
discountAmt = subtotal * discount,
finalTotal = subtotal - discountAmt
in
finalTotal
// Returns 269.892
Tip
M follows standard mathematical operator precedence: multiplication and division before addition and subtraction. Use parentheses liberally when combining multiple operations — it makes your intent explicit and prevents subtle calculation errors, especially when others maintain your queries.
You can compare numbers with =, <> (not equal), <, >, <=, and >=. These always return a logical value (true or false).
let
threshold = 1000,
actualRevenue = 1247.50,
metTarget = actualRevenue >= threshold // true
in
metTarget
M's number type includes three special values you'll encounter in practice:
#infinity — the result of dividing by zero (e.g., 1 / 0)-#infinity — negative infinity#nan — "Not a Number," the result of undefined operations like 0 / 0Warning
1 / 0 in M does not throw an error — it silently returns #infinity. If your data contains zeros in a denominator column, you need to guard against this explicitly using an if expression or the try pattern. Silent infinities can corrupt downstream aggregations without any visible error.
let
safeDivide = (numerator as number, denominator as number) =>
if denominator = 0 then null else numerator / denominator
in
safeDivide(100, 0) // returns null instead of #infinity
M's text type holds a sequence of Unicode characters. Text literals are always enclosed in double quotes. If you need a literal double-quote character inside a text value, you double it:
let
productName = "Laptop Pro 15""", // ends with a literal "
region = "North America",
emptyString = ""
in
region
That emptyString value is important — it's a valid text value that happens to contain zero characters. It is not the same as null. We'll return to this distinction shortly.
You concatenate text values using the & operator:
let
firstName = "Margaret",
lastName = "Okafor",
fullName = firstName & " " & lastName
in
fullName
// Returns "Margaret Okafor"
The & operator also works for combining lists and records, which is worth remembering — the same symbol is overloaded for different types.
Warning
You cannot use & to concatenate a text value with a number directly. "Order #" & 1042 will throw a type error. You must convert the number to text first: "Order #" & Number.ToText(1042).
The Text.* namespace provides a rich library of string operations. A few you'll use constantly:
let
rawInput = " invoice_2024_Q3 ",
trimmed = Text.Trim(rawInput), // "invoice_2024_Q3"
upper = Text.Upper(trimmed), // "INVOICE_2024_Q3"
hasPrefix = Text.StartsWith(trimmed, "inv"), // true
length = Text.Length(trimmed), // 15
extracted = Text.Middle(trimmed, 8, 4) // "2024"
in
extracted
For much more depth on text operations — including parsing delimited strings, extracting structured values, and handling encoding issues — see Text Manipulation and String Functions in Power Query M: Parsing, Splitting, and Transforming Text Data.
Text values support the same comparison operators as numbers: =, <>, <, >, <=, >=. Ordering is based on Unicode code points.
let
status = "Closed",
isClosed = status = "Closed", // true
isOpen = status = "Open" // false
in
isClosed
Key insight
Text comparison in M is case-sensitive by default. "closed" = "Closed" returns false. When you're comparing user-entered strings or values from inconsistent sources, normalize case first with Text.Lower() or Text.Upper() on both sides of the comparison.
M's logical type has exactly two values: true and false. These are language keywords, not strings — true is fundamentally different from "true". That distinction trips up almost everyone working with data exports that store boolean flags as text.
let
isActive = true,
isArchived = false,
isVisible = not isArchived // true
in
isVisible
M provides three logical operators:
| Operator | Meaning | Example |
|---|---|---|
and |
Both must be true | isActive and not isArchived |
or |
At least one is true | isPriority or isVIP |
not |
Inverts the value | not isDeleted |
let
accountAge = 365,
accountValue = 15000,
isPremium = accountAge > 180 and accountValue >= 10000
in
isPremium // true
M evaluates and and or expressions with short-circuit logic: if the left side of an and is false, M doesn't evaluate the right side. Similarly, if the left side of an or is true, the right side is skipped.
This is practically useful for guarding against errors:
let
text_value = "12.5",
isNumeric = text_value <> null and
Value.Is(Value.FromText(text_value), type number)
in
isNumeric
If text_value were null, the second condition would never evaluate — preventing a potential error from Value.FromText(null).
Tip
Use short-circuit evaluation deliberately when your second condition might error on certain inputs. Put the "safe" guard check on the left side of and, and the potentially dangerous check on the right.
Every comparison expression — >, <, =, <>, etc. — returns a logical value. This is what makes if expressions and filter predicates work:
let
orders = {1500, 230, 4200, 89, 3100},
highValue = List.Select(orders, each _ > 1000)
in
highValue
// Returns {1500, 4200, 3100}
The expression each _ > 1000 is a function that returns a logical value for each element. The List.Select function keeps elements where the predicate returns true. To understand each and predicate patterns in depth, Conditional Logic and Dynamic Column Selection in Power Query M covers this territory thoroughly.
null in M represents the absence of a value. It is not zero, not an empty string, not false — it is explicitly nothing. This distinction is not semantic pedantry; it has real consequences for how your expressions behave.
let
emptyText = "", // a text value with no characters
zeroNumber = 0, // a number with the value zero
nothingAtAll = null // no value at all
in
nothingAtAll
The most critical thing to understand about null is that it propagates through most operations. When you perform an operation where any operand is null, the result is typically null rather than an error.
let
price = 49.99,
taxRate = null,
totalPrice = price * (1 + taxRate) // returns null
in
totalPrice
This isn't an error — M simply returns null because it can't compute with an absent value. This propagation is intentional: M is designed for data processing where missing values are common and you often want them to "flow through" rather than crash your query.
Warning
Null propagation can silently corrupt aggregate results. If a column has even one null and you're not accounting for it, functions like List.Sum will return a sum anyway (nulls are ignored), but List.Average will also ignore nulls — which may or may not be what you want. Always audit your null-handling strategy deliberately.
Here's a behavior that surprises almost everyone: comparing anything to null using = returns null, not true or false.
let
result1 = null = null, // null (not true!)
result2 = null = "hello", // null (not false!)
result3 = null <> null // null
in
result1
This means you cannot test for null with = null in a predicate. Use Value.Is(value, type null) or the more idiomatic approach:
let
checkValue = (val) =>
if val = null
then "missing"
else Text.From(val)
in
checkValue(null) // "missing"
Wait — didn't we just say val = null returns null, not true? Yes. But in an if expression, M treats null as falsy: a null condition routes to the else branch. So if null then "a" else "b" returns "b". This is consistent, but it means you need to be intentional.
The cleaner, unambiguous way to check for null is value = null inside an if expression (which works due to null-as-falsy in conditionals) or using the dedicated function Value.Is:
let
isNull = (val) => Value.Is(val, type null),
testA = isNull(null), // true
testB = isNull(""), // false
testC = isNull(0) // false
in
testB
A common pattern in M queries is replacing null with a default value. M doesn't have a dedicated null-coalescing operator, but the idiomatic pattern is:
let
rawDiscount = null,
discount = if rawDiscount = null then 0 else rawDiscount,
basePrice = 199.99,
finalPrice = basePrice * (1 - discount)
in
finalPrice // 199.99
Tip
When you have several columns that need null replacement, Table.FillDown, Table.ReplaceValue, and Table.TransformColumns are more efficient than column-by-column if expressions. But at the M language level, the if val = null then default else val pattern is your building block.
M is a strongly typed language, which means it generally won't silently convert between types when doing so would be ambiguous or lossy. When you try to concatenate a number and a text value directly, you'll get an error rather than automatic conversion.
However, M does perform some implicit conversions — specifically when working with its Value and type-inference systems. The most common case is with Value.FromText, which attempts to infer the type of a string:
let
parsed = Value.FromText("42.5") // returns the number 42.5, not text
in
parsed
For explicit, controlled conversions:
let
numAsText = Number.ToText(1042), // "1042"
textAsNum = Number.FromText("1,042.50"), // 1042.5 (locale-aware)
logicalAsNum = Number.From(true), // 1
numAsLogical = Logical.From(1) // true
in
logicalAsNum
The relationship between types and coercion goes deep in M, and understanding nullable types and explicit casting patterns is essential for building robust pipelines. M Language Data Types and Type Coercion in Power Query covers this comprehensively.
Open Power Query Editor in Power BI Desktop or Excel. Create a blank query (in Power BI: Home tab → Transform data → New Source → Blank Query; in Excel: Data tab → Get Data → From Other Sources → Blank Query). Open the Advanced Editor and paste the following:
let
// Raw data as it might arrive from a CSV export
rawPrice = "1,249.00",
rawQuantity = "8",
rawDiscount = null,
rawIsActive = "TRUE",
// Type conversions
price = Number.FromText(Text.Remove(rawPrice, ",")),
quantity = Number.FromText(rawQuantity),
discount = if rawDiscount = null then 0.0 else Number.FromText(rawDiscount),
isActive = Text.Upper(rawIsActive) = "TRUE",
// Calculations
subtotal = price * quantity,
discountAmount = subtotal * discount,
finalTotal = subtotal - discountAmount,
// Output record
result = [
Price = price,
Quantity = quantity,
Discount = discount,
IsActive = isActive,
Subtotal = subtotal,
FinalTotal = finalTotal,
StatusNote = if isActive then "Active" else "Inactive"
]
in
result
Work through this exercise by modifying values:
rawDiscount from null to "0.15" and observe how the final total changes.rawIsActive to "false" and verify the StatusNote updates correctly.Text.Remove(rawPrice, ",") call and observe what error Number.FromText produces on a number with a thousands separator.rawQuantity to null and observe how null propagation affects subtotal and finalTotal."Expression.Error: We cannot convert the value null to type Number"
This happens when you pass null to a function that requires a concrete number. Guard with a null check: if value = null then 0 else Number.FromText(value). For a systematic approach to handling these errors gracefully, see Error Handling and Try-Otherwise Patterns in M.
Comparison returns null instead of true or false
You're comparing something to null using =. Remember that null = null is null. Use if val = null (which treats null as falsy) or Value.Is(val, type null) for explicit, readable null checking.
Text concatenation with & throws a type mismatch error
You're trying to combine a number, date, or other non-text value with text using &. Wrap the non-text value with its *.ToText() conversion function first.
Case-sensitive string comparison producing wrong results
Add Text.Lower() or Text.Upper() on both sides of the comparison. Never assume incoming data has consistent casing.
1 / 0 not throwing an error when you expect it to
M returns #infinity instead. Use Number.IsNaN and number = #infinity checks in your data quality validation, or guard all divisions with an if denominator = 0 check.
Numbers stored as text from an external system
CSV and Excel exports frequently store numeric columns as text, especially when they contain formatting characters like $, %, or ,. Use Text.Remove or Text.Select to strip non-numeric characters before converting with Number.FromText.
You now have a solid foundation in how M represents and operates on its four primitive types. Let's crystallize the key takeaways:
number type (64-bit float). Division always returns a decimal. Guard zero denominators explicitly.text values are Unicode, case-sensitive, and must be explicitly converted to or from other types — & won't auto-convert.logical has exactly two values (true/false). Comparison operators produce logicals. Null-as-falsy applies in if conditions.null means absent, not zero or empty. It propagates through most operations. Test for null with if val = null or Value.Is(val, type null), never with val = null as a standalone boolean check.With these primitives understood, you're ready to work effectively with the more complex value types that M builds on top of them: lists, records, and tables. The operations you've learned here — comparison, arithmetic, logical combination — appear constantly when you iterate over lists or transform table rows. List and Record Operations in M: Transform, Select, and Combine Data Structures is the natural next step.
If you're heading toward building reusable transformation logic, you'll want to understand how these primitive types participate in function signatures and type constraints — which is covered in Writing Custom M Functions from Scratch in Power Query.