Power Query We Cannot Convert The Value Error

This draft will explain Power Query We Cannot Convert The Value Error with practical examples, edge cases, and reporting patterns for analysts who live in spreadsheets and BI tools.

Overview

The Power Query message "We cannot convert the value..." is one of the most common error families in M-based data workflows because it appears whenever Power Query tries to force a value into a type or structure that does not match.

That is the core idea.

Power Query works with explicit value types such as text, number, date, datetime, datetimezone, logical, list, record, and table. If a step assumes the wrong type or tries to convert a value that does not fit the requested type, the query can stop with an error like:

• Expression.Error: We cannot convert the value null to type Text
• Expression.Error: We cannot convert the value #datetime(...) to type DateTimeZone
• Expression.Error: We cannot convert the value "ABC" to type Number

The exact wording changes, but the pattern is the same: Power Query expected one kind of value and received another.

This guide explains what this error usually means, the most common reasons it appears, how to isolate the failing step, and how to decide whether the right fix is source cleanup, a different type-conversion step, or a change in the query logic itself.

What the error usually means

A useful way to think about this error is:

Power Query is trying to evaluate an expression that expects a specific type or object shape, but the current value does not conform to that expectation.

This can happen in several ways:

• text is being converted to number and some rows are not numeric
• a date or datetime value is being treated as datetimezone without the right conversion
• null is being forced into a text or record operation without handling
• a query step expects a table but receives a record, list, or scalar value
• a merge, expand, or custom column step is built on the wrong data assumption
• automatic type detection picked a type that works for the first rows but fails later

The important point is that this is not just a generic formula failure. It is usually a very specific mismatch between:

• the actual value and
• the type or structure Power Query expects

Why this error is so common in Power Query

Power Query sits between messy source data and structured outputs.

That means it often has to deal with:

• imported CSVs
• vendor files
• inconsistent text values
• nulls
• mixed date formats
• partial schema changes
• merged queries
• nested records or lists
• type-conversion steps added automatically

Microsoft’s current Power Query documentation explains that M uses types to classify values into a more structured data set and that values can be converted between types using type-conversion functions and transformation steps. Microsoft’s current data-type documentation also says data types are defined at the field level and that values in a field are expected to conform to that type. citeturn206982search3turn206982search5turn206982search17

That is why this error is so common: Power Query is constantly trying to make raw values conform to a defined shape.

The most common causes of “We cannot convert the value…”

1. A Changed Type step picked the wrong type

This is one of the biggest real-world causes.

Power Query often adds a Changed Type step automatically. If that step assumes a column is numeric, date, or another type, later values that do not match can cause conversion failures.

Examples:

• a number column contains N/A
• a date column contains text notes in one row
• a postal code column was inferred as number but should have been text
• a source export mixes blank strings, numbers, and labels in one column

Microsoft’s current “Common Authoring Issues” documentation specifically says Power Query might incorrectly detect a column's data type because it infers types using only the first 200 rows. That means the query can look correct in preview but fail later when more rows are evaluated. citeturn206982search2

This is one of the first things to check.

2. A function is being used against the wrong value type

Sometimes the problem is not in a Changed Type step but in a later function call.

Examples:

• Text.From or other text logic used on unexpected nested data
• number conversion used on mixed text
• date conversion used on invalid date text
• a record operation used on null
• a table transformation used on something that is not a table

Microsoft’s type-conversion documentation makes it clear that conversion works only when the value can actually be represented as the requested type. The Text.From documentation also notes that it supports specific value kinds such as number, date, time, datetime, datetimezone, logical, duration, or binary, and returns null when the input is null. citeturn206982search3turn206982search9

That means if the value is outside what the function expects, conversion can fail.

3. A null is being treated like a normal value without handling

Null-related conversion problems are extremely common.

Examples:

• custom text logic assumes a value exists in every row
• a merged column is empty for some records
• a list or record expansion hits rows where the nested object is null
• a custom column uses text or record access against a null

This often produces errors like:

• We cannot convert the value null to type Text
• or similar variants

The fix is often not just “replace errors.” It is to decide how nulls should be handled before the conversion or access step.

4. A datetime, date, or timezone value is being converted incorrectly

This is another common family of problems.

For example, Microsoft Q&A includes cases where: Expression.Error: We cannot convert the value #datetime(...) to type DateTimeZone appears because a datetime value is being passed into logic expecting a datetimezone value. citeturn206982search0

This matters in workflows involving:

• imported timestamps
• timezone normalization
• merged date/time sources
• API data with different time representations
• Power BI models expecting one time type while the query holds another

These errors often look mysterious until you inspect the exact value and target type.

5. The query expects the wrong object shape

Power Query does not only work with scalar values. It also works with:

• tables
• lists
• records
• functions

If a step expects a table but receives a record, or expects text but receives a list, conversion-style errors can appear because the value is being treated as the wrong kind of object.

This is common in:

• JSON and API imports
• expansion steps
• custom columns using nested objects
• merge workflows
• list transformations
• query outputs that changed shape earlier in the pipeline

These errors are often fixed by checking the output of the previous step rather than the current step alone.

6. Source data changed but the query logic did not

A query can be perfectly valid today and fail tomorrow if the source changes.

Examples:

• a numeric column now includes a status label
• a file includes a note row
• an empty field becomes a nested object
• a timestamp format changes
• an API returns a new structure in some records

That is why this error is often part data problem and part query-maintenance problem.

Practical examples

Example 1: Text value in a numeric column

Problem: A Changed Type step converts Amount to number, but one row contains N/A.

Issue: The conversion fails because N/A is not a number.

Fix:

• replace or handle the nonnumeric values first
• or clean the source earlier
• or use controlled error handling after understanding the bad values

Example 2: Null to text conversion

Problem: A custom text step assumes every value in [CustomerName] exists, but some rows are null.

Issue: The query tries to treat null like text.

Fix: Handle null explicitly before applying text operations.

Example 3: Datetime to datetimezone mismatch

Problem: A query uses timezone logic on a plain datetime value.

Issue: The value is a datetime, but the function expects datetimezone.

Fix: Use the appropriate conversion path and confirm what time type the step is really holding before the transformation. citeturn206982search0turn206982search3

Example 4: Wrong object type after expansion logic

Problem: A later step expects a nested table but the earlier transformation now returns a record or null for some rows.

Issue: The conversion-style message appears because the query logic is assuming the wrong object type.

Fix: Inspect the previous step carefully and confirm what type each row actually contains.

How to inspect the error properly

A lot of users see the message and immediately try to rewrite the whole query.

That is usually slower than inspecting the error details first.

Microsoft’s current “Dealing with errors” documentation says Power Query exposes:

• error reason
• error message
• error detail and distinguishes between step-level and cell-level errors. citeturn206982search1

That matters because the detail often tells you exactly what Power Query received.

For example:

• which value failed
• which target type was expected
• whether the issue is on one row or the whole step
• whether the query failed as a step or only certain cells errored

This is often the fastest path to the real fix.

Step-level errors vs cell-level errors

This distinction is critical.

Step-level error

The whole step fails. Examples:

• the query tries to convert a nonmatching object type at the step level
• a transformation cannot run at all on the current structure

Cell-level error

The step completes, but some rows contain errors. Examples:

• most rows convert correctly but a few bad rows fail
• some imported values break a type conversion
• null or malformed values only affect certain records

If the error is cell-level, you often need to inspect the bad rows. If it is step-level, you usually need to fix the step logic itself.

Step-by-step workflow

Step 1: Find the first step where the error appears

Go through Applied Steps and identify the earliest step that fails.

Do not start at the end of the query unless the failing step really is the last one.

Step 2: Read the full error detail

Check:

• what value failed
• what type Power Query expected
• whether it is step-level or row-level

Step 3: Inspect the previous step’s output

Ask:

• what type of value is this column or object really holding?
• is it text, number, date, datetime, datetimezone, list, record, or table?
• is the source inconsistent across rows?

Step 4: Check for an automatic Changed Type step

This is one of the first suspects, especially after imports.

If needed:

• remove it temporarily
• move it later
• or redefine the types explicitly

Step 5: Handle bad values before conversion

For example:

• replace N/A
• standardize blanks
• clean malformed date text
• handle nulls explicitly

Step 6: If needed, use controlled error handling

Microsoft’s Power Query error-handling documentation explains try ... otherwise for catching and managing expected errors. citeturn206982search7

Use it when:

• bad values are expected
• fallback logic makes sense
• the query should keep working with controlled exceptions

But do not use it to hide a design you do not understand.

When to use try ... otherwise

try ... otherwise is useful when you expect occasional invalid values and want a predictable fallback.

Example pattern:

try Number.From([Amount]) otherwise null

This can help when:

• source data is imperfect
• a few rows are known exceptions
• you want the query to continue and then review bad rows separately

But the stronger rule is: first understand why the conversion fails, then decide whether it should be prevented or handled. Microsoft’s current error-handling docs support this pattern by showing that errors can be caught and managed deliberately rather than blindly suppressed. citeturn206982search7

How automatic type inference can create this error later

One of the trickiest parts of this error is that the query may look fine at first.

Because Power Query may infer types from the first 200 rows only, a column can appear safe in preview and then fail later during refresh when a different kind of value appears deeper in the data. Microsoft’s current “Common Authoring Issues” article explicitly warns about this behavior. citeturn206982search2

That means a good troubleshooting habit is:

• do not trust the first preview alone
• inspect later rows
• validate source consistency
• do not leave critical type inference entirely on autopilot

Common mistakes when fixing this error

Mistake 1: Replacing the error without understanding it

This can hide source problems that will keep coming back.

Mistake 2: Assuming the source column is clean because the preview looks clean

Type inference can make this misleading.

Mistake 3: Treating a null problem like a syntax problem

Sometimes the expression is fine and the source values are the issue.

Mistake 4: Assuming every row has the same object shape

Nested data can vary across records.

Mistake 5: Leaving automatic Changed Type too early in the query

Sometimes type conversion belongs later, after cleanup.

FAQ

What does 'We cannot convert the value' mean in Power Query?

It usually means Power Query is trying to convert a value into a type it does not match, or it is treating the value like the wrong kind of object during query evaluation.

How do I fix 'We cannot convert the value' in Power Query?

Start by locating the failing step, then read the full error detail to see the actual value and target type. After that, fix the type conversion, clean the source value, or correct the query logic that made the wrong assumption.

Can automatic type detection cause this error?

Yes. Power Query can infer the wrong type, especially because it may infer based on only the first 200 rows, so later values can fail when the type-conversion step runs.

Should I use try otherwise for this error?

You can use try otherwise when you expect occasional bad values and want controlled fallback logic, but it should not replace understanding why the conversion is failing.

Final thoughts

The Power Query message "We cannot convert the value..." usually looks intimidating, but it is often very specific once you inspect it properly.

Most of the time, it comes from one of a few practical causes:

• a Changed Type step is wrong
• the source data is inconsistent
• a null is being treated as a normal value
• a date or time type is mismatched
• the query assumes the wrong kind of object

That is why the best fix is usually not rewriting the whole query. It is isolating the first failing step, reading the exact error detail, and then deciding whether the right answer is:

• source cleanup
• a better type-conversion step
• explicit null handling
• or corrected query logic

Once you approach the error that way, it becomes much easier to diagnose and much easier to prevent in future refreshes.