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:
null propagation can silently break your logicInt64.From, Text.From, Date.From, etc.) and when to use eachtry...otherwise, Value.Is, and type-checking patternsThis 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.
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 characterslogical — true or falsedate — a calendar datetime — a time of daydatetime — combined date and timedatetimezone — datetime with timezone offsetduration — a span of timebinary — raw byte datanull — 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.
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.
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.
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.
Tip: Get into the habit of asking "can this column ever be null?" before writing arithmetic on it. Check source data for nullability before building transformations, not after hitting production errors.
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 |
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.
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:
Text.Replace([order_amount], "$", "") — removes the dollar signText.Replace(..., ",", "") — removes the thousands separatorNumber.From(...) — converts the clean text to a numberNow "$1,450.00" becomes 1450.00. This is explicit casting working alongside text manipulation — a very common pattern in real data cleaning work.
Warning:
Number.Fromrespects your system's locale settings for decimal separators. If you're building queries that will be used across regions where.and,have different meanings as separators, useNumber.FromTextwith an explicit locale:Number.FromText("1.450,00", "de-DE")for German formatting.
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.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.
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."
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.
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.
Tip: Don't use
try...otherwiseas a way to ignore all errors indiscriminately. Use it surgically, at the specific point where you expect a predictable class of error. Broad error suppression hides real bugs.
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.
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.
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 ordersdiscount_pct is blank (null) for orders without a discountStep 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.
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.
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:
try...otherwise, including the Error.Record and structured error surfacing for ETL audit logsThe 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.