Expression.Error: We cannot convert Text to Number (Handling “Dirty” data).

In Power Query, Expression.Error: We cannot convert to a number or DataFormat.Error: We couldn't parse the input provided as a Number occurs when a column transformation attempts to coerce a text string into a numeric data type, but encounters characters that violate mathematical formatting rules. Common culprits include currency symbols, accidental spaces, letters, regional punctuation differences, and alphanumeric placeholders like “N/A” or “None”.

Quick Answer

Clean your text column before changing its data type. Remove non-numeric characters using Transform > Replace Values (or Text.Select), replace text place markers like "N/A" with null, and verify your regional settings under Using Locale if your numbers use commas instead of periods for decimals. Never apply a type conversion directly over unscrubbed strings.

---

Why the Conversion Breaks

When you select a column and set its type to Whole Number or Decimal Number, Power Query applies the Table.TransformColumnTypes function. Under the hood, this calls the engine’s internal numeric parser on every single row.

If the parser encounters pure digits like "125", it successfully casts the value to 125.

However, if a single cell contains something like "$125.00", "125 EA", " 125 ", or "-", the parser aborts for that row. In the Power Query preview window, this shows up as red underlines and cell-level Error indicators. If you try to load this data into the Excel data model or a worksheet, the entire refresh fails with:

Expression.Error: We cannot convert Text to Number.

---

Step-by-Step Fixes for Common Data Quality Issues

1. Strip Hidden Spaces and Invisible Characters

Often, numbers imported from web exports, ERP systems, or mainframe reports contain leading spaces, trailing spaces, or non-breaking spaces (ASCII 160). These look like normal blanks, but standard conversion routines will not parse them.

1. Select the affected text column.
2. Go to the Transform tab.
3. Click Format > Trim to remove standard leading and trailing spaces.
4. Click Format > Clean to strip out non-printable ASCII characters.
5. Apply your numeric data type conversion.

2. Remove Currency Symbols, Commas, and Units

If your column contains values like "$1,450.50 USD" or "500 kg", you must remove the non-numeric text before converting.

• Using the User Interface:
  1. Right-click the column header and select Replace Values.
  2. In Value to Find, type the symbol or string (for example, $).
  3. Leave Replace With completely blank to delete it.
  4. Repeat for any other offending strings (such as commas or unit labels).
• Using M-Code to Keep Only Numbers and Decimals:
  If your column has unpredictable text mixed with numbers, you can use Text.Select inside a custom column to strip everything except digits, minus signs, and decimal points: #"Sanitized Text" = Table.TransformColumns( #"Previous Step", {{"Amount", each Text.Select(_, {"0".."9", ".", "-"}), type text}} ) Once sanitized, change the column type to type number.

3. Handle Text Placeholders (“N/A”, “TBD”, “Zero”, “-“)

Many systems export missing numerical values as hyphens ("-"), "N/A", or text notes. Because a numeric column cannot store text, these entries must be turned into nulls or zeros.

1. Select the column.
2. Go to Transform > Replace Values.
3. Set Value to Find to N/A (or your specific text placeholder).
4. Set Replace With to null (all lowercase, no quotes). In Power Query, null represents a blank cell.
5. If your accounting rules require hyphens to mean zero, set Replace With to 0.
6. Now convert the column to Decimal Number.

4. Resolve Regional Settings (Commas vs. Periods)

In the United States and UK, 1,250.50 uses a comma as a thousands separator and a period as a decimal point. In many European and Latin American formats, 1.250,50 uses a period for thousands and a comma for decimals. If you parse European numbers using US settings, the conversion fails.

1. Right-click the column header.
2. Select Change Type > Using Locale….
3. Under Data Type, select Decimal Number.
4. Under Locale, select the country of origin for the incoming file (e.g., German (Germany) or French (France)).
5. Click OK.

For broader strategies on managing mixed dates, times, and regional settings across queries, see Handling “Locale” errors: Importing DMY dates into an MDY system.

---

Defensive Handling: Auditing and Replacing Remaining Errors

If your dataset contains millions of rows and unexpected dirty values continue to break your refresh, you have two options for handling unparseable cells:

1. Replace Errors with Null or Zero:
   Right-click the column header and choose Replace Errors. Enter 0 or null. This prevents the entire query from breaking when unexpected text slips through.
2. Audit Dirty Rows:
   Before removing the errors, create a diagnostic branch. Right-click the query in the left pane, choose Reference, and in the new query, select the column and choose Home > Keep Rows > Keep Errors. This isolates every single offending row so you can identify which source records are corrupting your data pipeline.