Understanding M Language Data Types and Type Coercion: Nullable Types, Explicit Casting, and Type Mismatch Resolution in Power Query

Introduction

You've built a Power Query transformation that works perfectly against your development data. You promote it to production, connect it to the live database, and immediately hit a wall: Expression.Error: We cannot convert the value null to type Number. Or maybe the column you're trying to sum is silently treating your revenue figures as text, and your totals are just... wrong. No error, no warning — just bad numbers quietly poisoning your reports.

These problems have a root cause: M Language's type system is doing something you didn't expect, and you don't yet have the vocabulary to diagnose or fix it. The M Language — the formula language behind Power Query — has a type system that is both precise and flexible, and that combination creates genuine gotchas for anyone who doesn't understand how it works under the hood.

By the end of this lesson, you'll understand exactly how M handles data types, why null is its own category of headache, how to explicitly convert values from one type to another, and how to write defensive transformations that resolve type mismatches gracefully instead of crashing. These are the skills that separate someone who builds fragile queries from someone who builds reliable, production-grade data pipelines.

What you'll learn:

• How M's primitive and structured data types work, and why type information matters at query time
• What nullable types are, how they interact with operations, and why null propagation can silently break your logic
• How to use explicit type casting functions (Int64.From, Text.From, Date.From, etc.) and when to use each
• How to diagnose and resolve type mismatch errors using try...otherwise, Value.Is, and type-checking patterns
• How Power Query's "Change Type" step differs from what happens in M code — and why that distinction matters

---

Prerequisites

This lesson assumes you're comfortable opening Power Query Editor, writing basic M expressions in the formula bar, and understanding what a query step is. You should have seen at least one custom column formula before. You don't need to know the full M Language spec — we'll build from the ground up on types specifically.

---

How M Thinks About Types

Before we can fix type problems, we need to understand what M thinks a type is.

In M, every value has a type. A type is essentially a classification that tells M what kind of value it's dealing with and what operations are legal on it. Think of it like the difference between a date printed on a label and the same characters typed into a text field: visually they might look identical, but one lets you calculate how many days until the deadline, and the other just sits there as letters.

M's core primitive types are:

• number — any numeric value (M doesn't distinguish integer from decimal at the value level by default)
• text — a string of characters
• logical — true or false
• date — a calendar date
• time — a time of day
• datetime — combined date and time
• datetimezone — datetime with timezone offset
• duration — a span of time
• binary — raw byte data
• null — the explicit absence of a value (we'll revisit this in detail)

On top of primitives, M has structured types: list, record, and table. These are containers that hold other values.

When you look at a column in Power Query Editor and see a small icon in the column header — "ABC" for text, "123" for whole number, a calendar icon for date — you're seeing Power Query's type annotation for that column. That annotation drives behavior everywhere from filtering to DAX consumption in Power BI.

Here's the important nuance: M is a lazy, expression-based language. Types can be declared explicitly, or they can be inferred at runtime. When Power Query runs "Detect Data Type" or "Change Type" on a column, it's making a declaration. When you write an expression in a custom column without specifying a type, M infers it. Inference is convenient but can surprise you.

---

Nullable Types: When null Is a First-Class Citizen

This is where most type-related pain in Power Query originates, so let's spend real time here.

In M, null is not zero. It's not an empty string. It is the explicit representation of an absent or unknown value. And in M's type system, null has its own type: type null.

Now here's where it gets interesting. M supports nullable types, written as type nullable X. This means "a value that is either of type X, or null." When Power Query infers or assigns types to columns, it almost always uses nullable types. Your column isn't typed as type number — it's typed as type nullable number. This distinction matters the moment you start doing arithmetic or string operations on it.

null Propagation

M follows a rule called null propagation: most operations that receive null as an input will return null as output, silently, without an error. This is intentional — it mirrors SQL's behavior with NULL values. But if you're not expecting it, it's treacherous.

Open Power Query Editor and try this in a blank query (Home tab → New Source → Blank Query, then in the formula bar type the following):

let
    revenue = null,
    tax_rate = 0.15,
    tax_amount = revenue * tax_rate
in
    tax_amount

The result is null. No error. null times anything is null. If this were flowing into a Total Revenue column in your report, you'd just silently get a blank cell.

Now consider a more realistic scenario: you have a sales table where some rows have a missing discount_pct column value. You write a custom column:

= [unit_price] * [quantity] * (1 - [discount_pct])

For every row where discount_pct is null, the entire expression returns null. Your calculated sale amount becomes null rather than just using the full price. This is null propagation at work.

Handling null Before It Propagates

The standard tool for null handling in M is the null coalescing pattern using ?? or an explicit if check:

// Using if-then-else
= [unit_price] * [quantity] * (1 - (if [discount_pct] = null then 0 else [discount_pct]))

// Using the ?? operator (null coalescing)
= [unit_price] * [quantity] * (1 - ([discount_pct] ?? 0))

The ?? operator means: "use the left side if it's not null; otherwise use the right side." It's cleaner for simple cases.

---

Explicit Type Casting: Converting Values Deliberately

Type casting means deliberately converting a value from one type to another. M doesn't do implicit casting the way some languages do — it won't silently turn the text "42" into the number 42 just because you tried to add it to another number. That would cause a type mismatch error.

M provides a family of conversion functions for explicit casting:

Target Type	Function	Example
64-bit Integer	Int64.From	Int64.From("1500") → 1500
Decimal Number	Number.From	Number.From("3.14") → 3.14
Text	Text.From	Text.From(42) → "42"
Date	Date.From	Date.From("2024-01-15") → #date(2024,1,15)
DateTime	DateTime.From	DateTime.From("2024-01-15 09:30:00")
Logical	Logical.From	Logical.From(1) → true
Duration	Duration.From	Duration.From(1) → 1 day

Why Not Just Use "Change Type" in the UI?

When you right-click a column header and choose "Change Type," Power Query writes a step using Table.TransformColumnTypes. This is a table-level operation — it applies a type annotation to the entire column. Behind the scenes, it's calling type conversion on every value in that column.

The difference is scope and control. Table.TransformColumnTypes operates on a whole column at once and throws an error at the row level if conversion fails for any single value. Explicit casting functions in custom column expressions give you row-level control — you can handle conversion failures per-row with error trapping.

Here's what Table.TransformColumnTypes looks like in M (you can see this in the Advanced Editor after adding a Change Type step):

#"Changed Type" = Table.TransformColumnTypes(
    Source,
    {
        {"order_date", type date},
        {"revenue", type number},
        {"quantity", Int64.Type}
    }
)

Note Int64.Type — that's the type literal for a 64-bit integer. You'll see this notation used alongside the function-based approach.

A Real Casting Scenario

Imagine you're connecting to a CSV export from a legacy CRM system. Order amounts come in as text like "$1,450.00" and "$234.50". You need numeric values for aggregation.

You can't just cast these directly — the dollar sign and comma will cause Number.From to throw an error. You need to clean first, then cast:

= Number.From(Text.Replace(Text.Replace([order_amount], "$", ""), ",", ""))

Reading inside-out:

1. Text.Replace([order_amount], "$", "") — removes the dollar sign
2. Text.Replace(..., ",", "") — removes the thousands separator
3. Number.From(...) — converts the clean text to a number

Now "$1,450.00" becomes 1450.00. This is explicit casting working alongside text manipulation — a very common pattern in real data cleaning work.

---

Type Checking: Knowing What You Have Before You Transform It

Sometimes you receive data from a source where you genuinely don't know what type a value is going to be — maybe it's from a dynamic API, or a column that sometimes contains numbers and sometimes contains error codes like "N/A". In these cases, you need to inspect the type of a value at runtime before deciding what to do with it.

Value.Is and Value.Type

Value.Type returns the type of a value as a type object. Value.Is checks whether a value matches a specific type. Together, they let you write conditional logic based on actual runtime types.

let
    mystery_value = "12345",
    is_text = Value.Is(mystery_value, type text),
    is_number = Value.Is(mystery_value, type number),
    actual_type = Value.Type(mystery_value)
in
    [
        IsText = is_text,      // true
        IsNumber = is_number,  // false
        TypeName = actual_type // type text
    ]

In a custom column formula applied to a heterogeneous column, this becomes:

= if Value.Is([raw_value], type number) then [raw_value]
  else if Value.Is([raw_value], type text) then Number.From([raw_value])
  else null

This pattern safely normalizes a column that might contain numeric values stored as either actual numbers or text representations.

---

Resolving Type Mismatches with try...otherwise

The most powerful tool for production-grade type handling in M is the try...otherwise expression. It's M's error-handling mechanism, and it's essential for building transformations that don't collapse when data doesn't behave.

try expression otherwise fallback means: "Attempt to evaluate expression. If it throws any error, return fallback instead."

Basic try...otherwise for Casting

Going back to our currency cleaning example, suppose some rows have truly unrecoverable values — cells that contain literal text like "VOID" or "PENDING" that can't be cleaned and converted. Without error handling, Number.From will throw and the entire column transformation fails.

With try...otherwise:

= try Number.From(Text.Replace(Text.Replace([order_amount], "$", ""), ",", ""))
  otherwise null

Now "$1,450.00" converts to 1450.0, but "VOID" returns null instead of crashing the query. You've moved from a brittle transformation to a resilient one.

Inspecting the Error with try...otherwise

try actually returns a record when used without otherwise. This lets you inspect what went wrong, not just whether something went wrong:

let
    result = try Number.From("VOID")
in
    result

This returns a record like:

[HasError = true, Error = [Reason = "Expression.Error", Message = "...", Detail = "VOID"]]

You can access parts of this:

let
    attempt = try Number.From("VOID")
in
    if attempt[HasError] then "Conversion failed: " & attempt[Error][Message]
    else Text.From(attempt[Value])

This is overkill for most situations, but invaluable when you're building audit columns that need to capture why a value couldn't be processed — for data quality reporting, for example.

---

The "Changed Type" Trap: When UI Steps Create Type Problems

There's a subtle issue that trips up intermediate Power Query users: the interaction between Power Query's automatic type detection and your manual transformations.

When Power Query loads data, it often adds an automatic "Changed Type" step based on the first 1,000 rows of data. If your first 1,000 rows are clean and the next 10,000 contain anomalies, that "Changed Type" step will fail silently or produce nulls for the bad rows — and you won't see it until you look at the data downstream.

More insidiously: if you add a custom column before a "Changed Type" step, your custom column is working with the original (untyped or incorrectly typed) data. Column order in the query step list matters.

Example: The Order of Steps Matters

Suppose your source data has a delivery_days column that arrives as text. The automatic type step converts it to a number. You then add a custom column calculating a delivery deadline:

// This custom column was added BEFORE the "Changed Type" step
= Date.AddDays([order_date], [delivery_days])

If delivery_days is still text at this point in the step sequence, Date.AddDays requires a number and will throw a type mismatch. The fix is either to reorder steps so the type change happens first, or to cast explicitly in your custom column:

= Date.AddDays([order_date], Int64.From([delivery_days]))

The explicit cast makes your formula independent of step order — it converts what it receives regardless.

---

Hands-On Exercise

Let's put all of this together in a realistic scenario. We'll simulate a raw data import and apply type-safe transformations.

Scenario: You've received a CSV export from an e-commerce platform. The data has these issues:

• unit_price is stored as text with currency symbols like "€45.99"
• quantity is mostly numeric but occasionally contains "OUT_OF_STOCK" for unfulfilled orders
• discount_pct is blank (null) for orders without a discount

Step 1: Open Power Query Editor. Go to Home tab → New Source → Blank Query. Open Advanced Editor (Home tab → Advanced Editor) and paste:

let
    // Simulated raw source data
    Source = Table.FromRecords({
        [order_id = "ORD-001", unit_price = "€45.99", quantity = "3", discount_pct = "0.10"],
        [order_id = "ORD-002", unit_price = "€120.00", quantity = "OUT_OF_STOCK", discount_pct = null],
        [order_id = "ORD-003", unit_price = "€8.50", quantity = "12", discount_pct = "0.05"],
        [order_id = "ORD-004", unit_price = "€67.25", quantity = "1", discount_pct = null]
    }),

    // Step 1: Clean and cast unit_price
    CleanPrice = Table.AddColumn(Source, "price_numeric",
        each try Number.From(Text.Replace([unit_price], "€", ""))
        otherwise null,
        type number
    ),

    // Step 2: Cast quantity, handling non-numeric values
    CleanQuantity = Table.AddColumn(CleanPrice, "qty_numeric",
        each try Int64.From([quantity]) otherwise null,
        Int64.Type
    ),

    // Step 3: Calculate line total with null-safe discount
    LineTotal = Table.AddColumn(CleanQuantity, "line_total",
        each
            let
                price = [price_numeric],
                qty = [qty_numeric],
                disc = [discount_pct] ?? "0"
            in
                if price = null or qty = null then null
                else price * qty * (1 - Number.From(disc)),
        type number
    )
in
    LineTotal

Step 2: Click Done. Observe the results. ORD-002 should have a null qty_numeric and null line_total because its quantity was "OUT_OF_STOCK". The other orders should have calculated totals.

Step 3: Modify the disc line to remove the ?? "0" fallback and observe that ORD-002 and ORD-004 now return null for line_total due to null propagation from discount_pct.

Step 4: Restore the fallback and add an audit column:

HasConversionError = Table.AddColumn(LineTotal, "data_quality_flag",
    each if [qty_numeric] = null and [quantity] <> null then "Invalid quantity"
         else if [price_numeric] = null then "Invalid price"
         else "OK",
    type text
)

This gives you a data quality column that explains why any given row has nulls — essential for production pipelines where stakeholders need to understand data gaps.

---

Common Mistakes and Troubleshooting

Mistake 1: Assuming "Change Type" is the same as explicit casting The UI step changes the column's type annotation at the table level. If any value in the column can't convert, the entire step may fail or silently produce nulls. Use explicit per-row casting with try...otherwise for columns you know are dirty.

Mistake 2: Forgetting that null propagates through almost all operations Arithmetic, string concatenation with &, date functions — they all propagate null. Before any operation on a potentially-null column, decide: should null stay null, or should it have a default? Make that decision explicitly with ?? or if...then.

Mistake 3: Using = null instead of = null correctly In M, null = null returns null, not true. This is different from most languages. Use value = null in if conditions — M treats a null condition as false, so if null then "yes" else "no" returns "no". Test your null checks against actual data.

Mistake 4: Locale-sensitive number parsing Number.From uses the system locale. If your Power BI Desktop is set to one locale and the report is deployed in another, formatted numbers may parse incorrectly. Use Number.FromText with an explicit culture string when parsing formatted numbers.

Mistake 5: Wrapping entire expressions in try instead of just the risky part try (complex_expression) otherwise null hides all errors in the complex expression, not just type errors. A bug elsewhere in the expression will silently become null. Be as narrow as possible: wrap only the specific conversion call.

---

Summary and Next Steps

You now understand the M Language type system at the level needed to build resilient, production-grade Power Query transformations. Let's anchor the key ideas:

M has a rich set of primitive types, and every value carries a type. Nullable types (type nullable X) are the norm in real data, and null propagates silently through operations — making explicit null handling essential, not optional. Explicit casting functions (Int64.From, Number.From, Date.From, etc.) give you deliberate control over type conversion at the row level. try...otherwise lets you handle conversion errors gracefully, turning crashes into controlled null values or diagnostic messages. And step order in your query matters — the type of a value at any step depends on all the steps before it.

These patterns work together. In practice, a production-quality transformation for a dirty numeric column looks like:

each try Number.From(Text.Trim([raw_column])) otherwise null

Simple, defensive, and explicit.

Where to go next:

• Explore M's type system for structured types — how to define and validate the shape of records and tables, not just scalar values
• Learn about custom type definitions in M, where you can declare named types for reuse across query functions
• Dive into query parameters and dynamic data source patterns — type handling becomes even more important when your source schema can change
• Practice error handling patterns beyond try...otherwise, including the Error.Record and structured error surfacing for ETL audit logs

The investment you've made in understanding types here will pay dividends in every query you build from this point forward — fewer silent errors, more predictable behavior, and dashboards your stakeholders can actually trust.