← Back to Error Directory

Power Query "Expression.Error" — Complete Fix Guide

Power Query's underlying M language throws one generic error label for dozens of causes — here's how to isolate exactly which step and which cause you're dealing with.

Table of Contents

1. What Expression.Error Actually Represents

Power Query is built on top of a functional programming language called M. When any step in a query fails to execute — for any of dozens of possible underlying reasons — the resulting error is broadly labeled "Expression.Error" along with a more specific (but often still fairly technical) description beneath it. Unlike worksheet formula errors that map cleanly to a specific label like #VALUE! or #REF!, Power Query's errors require reading the detailed error description text itself, not just the headline label, to understand the actual cause.

2. Interactive Demo: Applied Steps Isolation

Click through each step to see exactly where a query starts failing — the same technique used in the real Power Query Editor's Applied Steps pane.

This exact pattern — a Rename step followed by a Filter step still using the old name — is one of the most common real-world causes of Expression.Error.

3. Cause 1: Data Type Conversion Failures

When a "Changed Type" step attempts to convert a column to a specific data type (like Whole Number or Date) but encounters a value it cannot convert — such as text mixed into a numeric column, or an unusually formatted date string — that specific cell's value becomes an in-place error rather than failing the entire step immediately. This can surface later as Expression.Error when a downstream step tries to perform a calculation on that now-broken cell.

4. Cause 2: Referencing a Renamed or Removed Column

As shown in the interactive demo above, this is one of the most frequent real-world causes. Any step that explicitly names a column — Filter Rows, Add Custom Column, Group By, and others — breaks the moment an earlier step changes that column's name or removes it entirely, since the reference becomes stale.

5. Cause 3: Null Values Reaching Incompatible Operations

Power Query's null handling differs meaningfully from a blank Excel cell — certain M functions and operations don't gracefully accept null as an input, especially text functions expecting a string or arithmetic operations expecting a number. If your source data contains genuinely empty cells that flow into a step performing text manipulation or math, Expression.Error can appear specifically on rows where a null slipped through.

6. The Applied Steps Isolation Method

  1. Open the query in the Power Query Editor (Data tab → Queries & Connections → right-click the query → Edit).
  2. In the Applied Steps pane on the right, click each step from top to bottom, one at a time.
  3. Watch the preview pane update after each click — the exact step where the error first appears (rather than a valid preview) is your culprit.
  4. Once identified, click the gear icon next to that step (if available) to review and adjust its specific configuration, or right-click the step and select View Native Query / examine the formula bar for the underlying M code driving that step.

7. Using try...otherwise to Handle Errors Gracefully

For situations where a small number of rows are expected to fail a particular transformation (like a type conversion) but you want the query to continue rather than halting entirely, M's try...otherwise construct provides a fallback:

= Table.AddColumn(#"Previous Step", "SafeConversion", each try Number.From([Amount]) otherwise null)

This attempts the conversion, and if it fails for a specific row, substitutes null instead of causing the entire step to fail, allowing you to filter or investigate those specific problem rows afterward without losing the rest of the query's output.

8. Frequently Asked Questions

What is Expression.Error in Power Query?

Expression.Error is Power Query's generic error label for a failure in the M language code behind a query step, most commonly caused by a data type mismatch, an attempt to reference a column that doesn't exist, or a null value reaching an operation that can't handle it.

How do I find which step in Power Query is causing the error?

Click through each step listed in the Applied Steps pane on the right side of the Power Query Editor one at a time. The preview pane updates for each step, and the exact step where the error first appears is the one causing the problem.

Why does a column reference work in one step but fail in a later step?

This typically happens when an earlier step renames, removes, or changes the type of a column, and a later step still references the old column name or expects the original data type, causing the reference to break partway through the query.

Related Guides

Editorial Disclaimer: This guide is developed to the best of our domain knowledge and tested against Excel 2019+, Microsoft 365, and Google Sheets. Content is updated continuously as spreadsheet calculation engines evolve.