iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
Power Query’s “We couldn’t convert to Number” or “We couldn’t convert to Date” error means a value could not be interpreted as the type assigned to its column. Inspect the error cell and its detail first. Then check whether the source value is invalid, the column has the wrong type, or—especially for dates—the text is being read using the wrong locale. Choose a fix that preserves the data’s intended meaning rather than automatically replacing or deleting errors.
Find the failing value and step
In Power Query Editor, an error can affect a single cell or an entire step. A cell-level error appears as Error in the preview; it does not necessarily prevent the query from loading. A step-level error appears in an error pane and can prevent the query from loading. Select an error cell or open the pane to inspect its reason, message, and detail. That information can reveal the value and operation involved, whereas the generic conversion wording alone may not identify the cause. See Microsoft’s guide to dealing with Power Query errors.
Column quality and data profiling can help locate errors in a column. During diagnosis, use Keep errors to isolate affected rows for inspection; it identifies the problematic records but does not fix them.
Free tools Windows power users keep installed
One-click scans. No signup required.
Check whether the value and target type make sense
A conversion error often begins at a type-change step when one or more source values do not fit the selected type. For example, Microsoft documents NA in a column being converted to a whole number: that text cannot be interpreted as a number, so the affected cell becomes an error.
#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
Inspect the original value for text markers such as NA, blanks, unexpected symbols, or inconsistent formatting. Also verify that the column should be numeric at all. An account number may contain only digits but still be an identifier, not a quantity; treating it as text may better preserve its meaning. Microsoft’s guidance for Business Performance Planning describes account numbers as text fields and stresses matching source and destination types. That example is specific to that product, but the distinction between identifiers and measures applies when deciding what type your data represents.
For text intended to be a number, check whether decimal and thousands separators, currency symbols, or percent notation match the parsing rules. Microsoft’s M type-conversion reference documents functions such as Number.FromText and Value.FromText, including culture-aware conversion options. The appropriate interpretation depends on the source format and what the values mean.
Fix dates that are being read with the wrong locale
A date string can be valid but interpreted using a different day/month order or regional convention. For example, text written as day/month/year may be read using month/day/year conventions. Depending on the values, that can produce an error or a date that is valid but wrong. Microsoft notes that Power Query uses locale to interpret text and convert it to other data types. Power Query Desktop uses the operating system’s regional format by default; Power Query Online uses the project’s regional settings. The relevant default and available settings depend on the host.
- In Power Query Editor, right-click the affected column header.
- Choose Change type, then Using locale.
- Select the intended data type and a locale matching the source text’s date convention.
- Check the resulting day, month, and year against known source records, particularly if dates could be valid under either convention.
For an M-based transformation, Date.FromText supports optional Format and Culture fields. Use them when the input format is known; validate the parsed values rather than assuming that a displayed date confirms the intended interpretation. See Microsoft’s Date.FromText reference and Power Query data types documentation.
Choose a repair that fits the data
| Remedy | Use it when | Effect on the data |
|---|---|---|
| Correct the source or transformation | The source value is wrong, or the selected type does not represent the field correctly. | Can preserve the record while correcting its value or interpretation. |
| Convert using the matching locale or parsing rules | The text is valid but its separators, date convention, or other formatting differs from the current interpretation. | Preserves the source value while changing how Power Query reads it. |
| Replace errors | A specific replacement has a defensible meaning for the affected field. | Changes error cells to the chosen value; an arbitrary replacement such as zero can distort totals or later calculations. |
| Remove error rows | Excluding those records is acceptable for the analysis. | Drops the affected rows from the resulting table. |
| Keep errors | You need to audit or investigate the affected records. | Isolates rows with errors; it does not repair them. |
Use try … otherwise |
A deliberate fallback is appropriate when the preferred value cannot be converted. | Returns the fallback for an error, so the fallback must be valid for the business meaning of the field. |
Power Query provides UI actions for replacing or removing errors. Before using either, decide whether the record can be repaired, whether a replacement has a justified meaning, or whether dropping the row is acceptable. For conditional handling in M, Microsoft documents try … otherwise, including an example that tries [Standard Rate] and falls back to [Special Rate]. See Microsoft’s Power Query error-handling reference.
Practical troubleshooting order
- Locate the first step where the error appears and inspect its reason, message, and detail.
- Compare the failing source value with the column’s intended type. Check whether it is malformed, formatted differently, or actually an identifier rather than a measure.
- For date text, confirm the source’s date convention and apply a matching locale or explicit parsing format.
- Correct or normalize the value when possible. If the value is exceptional, choose a replacement, fallback, or row removal only when its effect on the result is acceptable.
- Refresh or re-evaluate the query and verify the repaired values against the source, including ambiguous dates and any affected totals.
The exact cause cannot be determined from the error wording alone: it depends on the failing value, the applied step, the host, and its locale settings. Microsoft also documents type mismatches as a cause of refresh errors in its Business Performance Planning dataflow example.
Quick Recap
Rank #4
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.
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 problems
