Do these 3 things before closing this tab:
1Clear out junk files and repair common Windows errors2Scan for outdated or missing drivers - takes under a minute3Repair Windows errors before they cause bigger problemsPower Query shows “We couldn’t convert to Number” or “We couldn’t convert to Date” when a value cannot be interpreted as the type assigned to it. Select the error and inspect its details before changing the data: the cause may be an incompatible value such as NA, or a valid date being read with the wrong locale. Then choose a fix that preserves the meaning of the data.
Find the failing value and conversion step
In Power Query, an error can affect a whole step or an individual cell. A step-level error appears in a yellow error pane and can prevent the query from loading. A cell-level error appears in the preview as Error and does not necessarily stop the query from loading.
As an Amazon Associate I earn from qualifying purchases.
Select an error cell to view its reason, message, and detail. These can reveal the input value and operation behind the generic conversion message. Column quality and data profiling can also help locate errors. During diagnosis, Keep errors can isolate affected rows for inspection; it does not fix them.
Check whether the value fits the intended type
Conversion errors often follow a step that changes a column’s type. Microsoft’s example is the text NA in a column being converted to a whole number. Inspect the source value for text markers, blanks, unexpected symbols, or mixed formats before deciding what to do.
#1 Best Overall
- The Microsoft Office 365 Bible: The Most Updated and Complete Guide to Excel, Word, PowerPoint, Outlook, OneNote, OneDrive, Teams, Access, and Publisher from Beginners to Advanced
- ABIS BOOK
Also confirm that the column should be numeric at all. An account number, for example, may contain digits but still be an identifier that should remain text. The source and destination types should match the data’s purpose.
For number text, check whether separators, currency signs, percent notation, or other formatting match the parsing rules. M includes functions such as Number.FromText; Value.FromText can accept a culture argument. The correct parsing choice depends on how the source values are represented.
Fix dates that are being read with the wrong locale
A date can be valid but interpreted using the wrong day-and-month order. For example, day/month/year text may fail or be misread when Power Query interprets it using month/day/year conventions. Microsoft explains that locale is used to interpret text values and convert them into other data types.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
In the interface, right-click the column header and select Change type > Using locale. Choose the target type and a locale that matches the source text. Desktop uses the operating system’s regional format by default; Power Query Online uses project regional settings. These defaults can be overridden through the relevant settings.
In M, Date.FromText accepts optional format and culture settings. For ambiguous dates, check the resulting day, month, and year rather than relying only on how the displayed string looks.
Choose an error-handling action that fits the data
| Action | Use it when | Effect on the data |
|---|---|---|
| Correct the source or transformation | The source value is wrong, or the assigned type is inappropriate. | Repairs the cause when the underlying value or type can be fixed. |
| Replace errors | A replacement value has a defensible meaning for the affected records. | Substitutes a value; an arbitrary replacement such as zero can distort totals or later calculations. |
| Remove error rows | Excluding the affected records is acceptable for the analysis. | Drops those rows from the resulting table. |
| Keep errors | You need to isolate records for diagnosis or auditing. | Retains the error rows; it does not repair them. |
Use try … otherwise |
A deliberate fallback makes sense for the data. | Uses the fallback when the expression errors; for example, try [Standard Rate] otherwise [Special Rate] tries the second field if the first errors. |
Microsoft documents the interface actions and M error-handling patterns in its guides to dealing with errors, error handling, and type conversion. Use removal only when losing those records is acceptable, and use replacement or fallback only when the resulting value remains meaningful.
Use explicit parsing in M when the source format is known
When you need a repeatable conversion, use an M conversion function that matches the input instead of relying on an unsuitable default. The Date.FromText reference documents format and culture options for dates; the type-conversion reference covers conversion functions including Number.FromText. For dates, set the source culture or format where appropriate, then verify the parsed result. For exceptional values, use try … otherwise only when its fallback is appropriate for the specific record.
Recommended Free Tools
A quick diagnostic sequence
- Select the error cell and read its reason, message, and detail.
- Find the step that assigned or converted the column type.
- Inspect the source value and confirm whether the column represents a number, date, identifier, or another kind of data.
- For dates or formatted numbers, match the parsing locale and format to the source.
- Correct the source or transformation where possible; otherwise, choose a replacement, removal, retained error, or fallback based on the consequences for those records.
Microsoft’s Power Query data types documentation describes locale behavior and the Using locale workflow. Its separate Business Performance Planning dataflow guidance illustrates why source and destination types must match, including keeping account numbers as text where appropriate.
Quick Recap
Best Value
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
