Wicked Smart Data
LearnInsightsAboutContact
Sign InLet's Build
LearnInsightsAboutContact
Sign InLet's Build
Wicked Smart Data

Intelligence, automation, and expert execution — plus an elite library of free knowledge. We turn complexity into competitive advantage.

Start a conversation

Platform

  • Learning Paths
  • Insights
  • RSS Feed

Company

  • About
  • Contact
  • Work With Us

Legal

  • Privacy Policy
  • Terms of Service

© 2026 Wicked Smart Data. All rights reserved.

Intelligence · Automation · Advantage

All Insights
Power Query

Understanding Value Types and Primitive Operations in M: Numbers, Text, Logical, and Null Foundations

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.

🌱 Foundation16 min readNov 17, 2026Updated Nov 17, 2026
Understanding Value Types and Primitive Operations in M: Numbers, Text, Logical, and Null Foundations
On this page
  • Introduction
  • Prerequisites
  • What "Value Type" Actually Means in M
  • Number: The Arithmetic Foundation
  • How M Represents Numbers
  • Arithmetic Operators
  • Comparison Operators on Numbers
  • Special Number Values
  • Text: Working with Strings
  • How M Represents Text
  • The Concatenation Operator
  • Essential Text Functions
  • Text Comparison
Logical: Boolean Values and Logic Operations
  • True, False, and Nothing Else
  • Logical Operators
  • Short-Circuit Evaluation
  • Logical Values from Comparisons
  • Null: The Absence of a Value
  • What Null Actually Is
  • Null Propagation: The Most Important Rule
  • Null in Comparisons
  • The Null-Coalescing Pattern
  • Type Coercion: When M Converts Automatically (and When It Won't)
  • Hands-On Exercise
  • Common Mistakes & Troubleshooting
  • Summary & Next Steps
  • Understanding Value Types and Primitive Operations in M: Numbers, Text, Logical, and Null Foundations

    Introduction

    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:

    • How M's four primitive types (number, text, logical, null) are defined and stored
    • Which arithmetic and comparison operators apply to each type, and why
    • How M handles implicit type coercion — and when it refuses to
    • The precise behavior of null and why it propagates through expressions
    • Common type-related mistakes and how to diagnose them quickly

    Prerequisites

    You 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.


    What "Value Type" Actually Means in M

    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.


    Number: The Arithmetic Foundation

    How M Represents Numbers

    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.

    Arithmetic Operators

    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.

    Comparison Operators on Numbers

    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
    

    Special Number Values

    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 / 0

    Warning

    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
    

    Text: Working with Strings

    How M Represents Text

    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.

    The Concatenation Operator

    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).

    Essential Text Functions

    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 Comparison

    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.


    Logical: Boolean Values and Logic Operations

    True, False, and Nothing Else

    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
    

    Logical Operators

    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
    

    Short-Circuit Evaluation

    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.

    Logical Values from Comparisons

    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: The Absence of a Value

    What Null Actually Is

    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
    

    Null Propagation: The Most Important Rule

    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.

    Null in Comparisons

    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
    

    The Null-Coalescing Pattern

    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.


    Type Coercion: When M Converts Automatically (and When It Won't)

    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.


    Hands-On Exercise

    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:

    1. Change rawDiscount from null to "0.15" and observe how the final total changes.
    2. Change rawIsActive to "false" and verify the StatusNote updates correctly.
    3. Try removing the Text.Remove(rawPrice, ",") call and observe what error Number.FromText produces on a number with a thousands separator.
    4. Set rawQuantity to null and observe how null propagation affects subtotal and finalTotal.

    Common Mistakes & Troubleshooting

    "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.


    Summary & Next Steps

    You now have a solid foundation in how M represents and operates on its four primitive types. Let's crystallize the key takeaways:

    • M has a single 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.

    Work With Us

    From insight to implementation

    Reading is the start. When you're ready to build the data, automation, or AI systems behind it, our team turns strategy into shipped results.

    Let's Build

    Advanced M Language

    Previous

    Implementing Custom Connection Retry and Backoff Logic in Power Query M: Building Fault-Tolerant Data Source Connectors for Unreliable Networks and Throttled APIs

    Related Insights

    Power QueryFoundation

    Promoting Queries to the Data Model vs. Connection Only in Power Query: Controlling What Loads and Why

    16 min
    Power QueryExpert

    Implementing Custom Connection Retry and Backoff Logic in Power Query M: Building Fault-Tolerant Data Source Connectors for Unreliable Networks and Throttled APIs

    28 min
    Power QueryExpert

    Implementing Custom Locale-Aware Number and Date Parsing Pipelines in Power Query M: Resolving Regional Format Conflicts When Combining Multi-Country Data Sources

    26 min

    On this page

    • Introduction
    • Prerequisites
    • What "Value Type" Actually Means in M
    • Number: The Arithmetic Foundation
    • How M Represents Numbers
    • Arithmetic Operators
    • Comparison Operators on Numbers
    • Special Number Values
    • Text: Working with Strings
    • How M Represents Text
    • The Concatenation Operator
    • Essential Text Functions
    • Text Comparison
    • Logical: Boolean Values and Logic Operations
    • True, False, and Nothing Else
    • Logical Operators
    • Short-Circuit Evaluation
    • Logical Values from Comparisons
    • Null: The Absence of a Value
    • What Null Actually Is
    • Null Propagation: The Most Important Rule
    • Null in Comparisons
    • The Null-Coalescing Pattern
    • Type Coercion: When M Converts Automatically (and When It Won't)
    • Hands-On Exercise
    • Common Mistakes & Troubleshooting
    • Summary & Next Steps