Common M Errors

These are the errors you're most likely to encounter when first writing or modifying M code. Each entry shows the error message, what causes it, and how to fix it.

`The field 'X' of the record wasn't found`

Full message: Expression.Error: The field 'ProductName' of the record wasn't found.

Cause: You're accessing a column that doesn't exist in the table at that step — either a typo, a capitalization mismatch, or the column was removed or renamed in an earlier step.

M column names are case-sensitive. [productname] and [ProductName] are different fields.

Fix: Check the exact column name. In the Advanced Editor, click on a step in the Applied Steps pane to inspect the table's columns at that point. Then verify the name matches exactly — including capitalization.

// Wrong — case mismatch
Table.SelectRows(Sales, each [productname] = "Widget")

// Correct
Table.SelectRows(Sales, each [ProductName] = "Widget")

`We cannot apply operator to types Null and Number`

Full message: Expression.Error: We cannot apply operator > to types Null and Number.

Cause: A column contains null values and you're using an operator (like >, <, +) that can't work with null. Null propagates silently through most expressions but causes an explicit error here.

Fix: Guard against null before comparing. The ?? operator provides a fallback when the left side is null:

// Errors when Quantity is null
Table.SelectRows(Sales, each [Quantity] > 0)

// Safe — treats null Quantity as 0 before comparing
Table.SelectRows(Sales, each ([Quantity] ?? 0) > 0)

See Null Handling for the full picture of how null flows through M expressions.

`We couldn't convert to Number` (or Date, or Text)

Full message: DataFormat.Error: We couldn't parse the input provided as a Number value.

Cause: Table.TransformColumnTypes is trying to convert a column to a type that some values don't match. Common culprits: text columns containing "-", "N/A", "(blank)", or empty strings being converted to a number or date.

Fix — clean first, then convert:

let
    // Replace the bad value with null before converting
    Cleaned = Table.ReplaceValue(Source, "-", null, Replacer.ReplaceValue, {"Amount"}),
    Typed   = Table.TransformColumnTypes(Cleaned, {{"Amount", type number}})
in
    Typed

Fix — convert with a per-cell fallback:

Table.AddColumn(Source, "AmountNum", each try Number.FromText([Amount]) otherwise null, type number)

This returns null for any value that can't be converted, rather than throwing an error.

`The name 'X' wasn't recognized`

Full message: Expression.Error: The name 'Filterd' wasn't recognized. Make sure it's spelled correctly.

Cause: You referenced a step name that doesn't exist — typically a typo. All step name references are case-sensitive, and step names containing spaces must use #"..." quoting.

let
    Source   = Sales,
    Filtered = Table.SelectRows(Source, each [Region] = "East"),
    Sorted   = Table.Sort(Filterd, {"UnitPrice", Order.Descending})  // typo: Filterd
in
    Sorted

Fix: Match the step name exactly.

Sorted = Table.Sort(Filtered, {"UnitPrice", Order.Descending})

If the step name has spaces (as generated by the UI), reference it with #"...":

Sorted = Table.Sort(#"Filtered Rows", {"UnitPrice", Order.Descending})

`Formula.Firewall: Query is accessing data sources that have privacy levels which cannot be used together`

Cause: Power BI's Formula Firewall prevents queries from combining data from sources with incompatible privacy levels (Public, Organizational, Private). This most commonly appears when you pass a value from one data source as a parameter to another — for example, filtering a database query with a value that came from a web API.

Fix for development: In Power BI Desktop, go to File → Options and settings → Options → Current File → Privacy and select "Ignore the Privacy Levels and potentially improve performance." This disables the firewall for that file.

Fix for published reports: Restructure the query to avoid mixing data sources inline. Use a Power Query parameter (a static value defined in the query parameters list) instead of pulling a filter value dynamically from another source at runtime.

`The key didn't match any rows in the table`

Full message: Expression.Error: The key didn't match any rows in the table.

Cause: You used the {[ColumnName = value]} drill-down syntax to look up a single row, but either no row matched or more than one row matched.

// Looks up the one row where ID = 42
// Errors if 0 rows match or if 2+ rows match
Sales{[ID = 42]}

Fix: Use Table.SelectRows with Table.First and a fallback value:

Table.First(Table.SelectRows(Sales, each [ID] = 42), null)

This returns null if no row matches instead of throwing an error, and returns the first match if multiple rows match.