Power Query Overflow Errors: Causes and Fixes

Power Query overflow errors occur when a value or calculation cannot fit the numeric or date type being used. Find the failing step and value first, then correct the source data, choose a suitable type, or change the calculation; simply replacing every error with null can conceal bad data.

What an overflow error means

Power Query applies data types while it imports and transforms data. An overflow generally means that a value falls outside the range supported by a type or operation. For example, a value that is too large for a whole-number conversion may fail even though it appears as ordinary digits in the source file. An operation can also create a value outside the supported range: adding a large amount to another amount, multiplying quantities, or moving a date beyond the valid date range are possible examples.

There is not one universal Power Query overflow message. The wording and error category depend on the step, connector, and underlying operation. A message saying that a value cannot be converted to Number may indicate malformed text rather than an overflow. Conversely, a connector or database may report a range or arithmetic error before Power Query has a chance to convert the value. Treat the message as a clue, and inspect the value and step that triggered it.

An error in the Power Query preview is not necessarily a problem with every row. A single unusual record can fail a conversion while the rest of the column looks fine. A query can also preview successfully and fail on refresh if the preview did not include the row that causes the problem.

Where to find the failing step and value

In Excel for Microsoft 365 on Windows, open Data > Queries & Connections, right-click the affected query, and select Edit. In Power Query Editor, review the Applied Steps list, usually at the right. Select steps from top to bottom to identify the first one that displays an error. The first failing step is especially useful: later steps may only be showing the consequences of an earlier conversion or calculation.

Click an Error cell to inspect its details. Depending on the connector and error, the details may identify the attempted conversion, the offending value, or the operation that failed. Compare that row with the source data, and check whether a step named Changed Type, an arithmetic custom column, or a date transformation was just applied. If the preview does not expose the bad record, inspect the source file or database directly and refresh the query. The preview is a diagnostic view, not proof that every source row has been checked.

Also check whether the error is in the query itself or in a later destination. A query may load into the editor but fail when loaded to a worksheet or data model because the destination has its own limits or type requirements. If the editor shows no error, check the refresh message and identify which query and load operation failed.

Why data types matter

Power Query’s Whole Number type represents signed 64-bit integers, with a range from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. A digit string outside that range cannot be represented as a whole number. Do not assume that a column should be Whole Number just because its values contain no decimal point: account identifiers, tracking codes, and long product numbers are labels, not quantities, and are usually better treated as text.

Decimal Number uses floating-point representation, so it supports a very wide range but does not preserve every decimal digit exactly. Fixed Decimal Number is intended for values such as currency that need fixed decimal places. It has different range and precision characteristics from Whole Number and Decimal Number, so changing a column to it is not a universal overflow cure. Choose a type based on what the values mean and how much precision the analysis requires.

Dates and times have valid ranges too. A date conversion can fail if the source contains an impossible date, a text value in an unexpected format, or a calculation that moves a date outside the supported range. Regional settings matter when parsing text: a value such as 03/04/2025 can mean different dates depending on whether the source uses month/day/year or day/month/year. That is a parsing issue, not necessarily an overflow, but it can lead to incorrect transformations that are hard to spot.

Common causes to check

Automatic type detection: Power Query may insert a Changed Type step based on the sample it sees. Later rows can contain larger numbers, decimals, blanks, or text that the inferred type does not accommodate.
Large identifiers treated as numbers: Long codes may exceed an integer range or lose meaningful digits when stored as a numeric value. Keep identifiers as text if arithmetic is not required.
Arithmetic results outside the chosen type: A multiplication or aggregation can produce a result much larger than any individual input. Check the units and expected maximum, not just the source column’s current values.
Mixed or malformed source values: A column may combine numbers with labels such as N/A, separators, currency symbols, or values imported in another locale.
Source-system limits: A database, file format, or connector can impose limits before Power Query receives data. The same query can behave differently when the source type or connector changes.
Out-of-range dates: Invalid source dates or date arithmetic can fail during conversion or transformation.

A practical troubleshooting sequence

Locate the first failing step. In the editor, select Applied Steps from the top down. Note the exact step and column where the first error appears.
Inspect the raw value. Go back to the preceding step, where the original value may still be visible. Check for unexpectedly long digits, decimal places, text mixed into the column, or an unusual date.
Confirm what the field represents. Decide whether it is a quantity, a measurement, money, a date, or an identifier. That decision determines whether numeric conversion is appropriate at all.
Check the source and locale. Compare the value in the original file or database. For text numbers and dates, confirm the decimal separator, thousands separator, and date convention.
Make the smallest safe change. Correct an incorrect source value, change an unsuitable type, or revise the operation. Refresh and verify both the formerly failing row and representative normal rows.
Check the final load. Refresh the query and confirm that the worksheet, data model, or other destination accepts the results.

For a quick test in Power Query Editor, select the relevant column and use Transform > Data Type to try a more suitable type. This changes the query transformation; it does not repair the source file. If the change makes the error disappear, still check whether the new type preserves the precision and meaning you need.

Choosing a fix without hiding bad data

If a value is genuinely too large for Whole Number but is a valid quantity, use a supported type that fits the data and downstream calculations. If it is an identifier, use text instead. For currency, consider Fixed Decimal Number when its fixed precision suits the task. If a source value is simply wrong, correcting it at the source is usually more reliable than adding a workaround to every refresh.

When a column legitimately contains occasional invalid entries, handle them deliberately. Power Query’s M language supports error handling with try. For example, a custom column expression such as try Int64.From([Amount]) returns a result record that indicates whether conversion succeeded. You can inspect its HasError, Value, and Error fields to separate valid values from failures. A shorter pattern, try Int64.From([Amount]) otherwise null, replaces failed conversions with null; use it only when null is an accepted outcome and you retain a way to count or review rejected rows. Otherwise, a refresh may look successful while silently omitting important data.

When modifying a query, preserve the original column until the revised result has been checked. For a production report, consider creating a separate error or exception query that retains rejected rows and the reason for rejection. That makes it easier to distinguish a new data-quality issue from a deliberate missing value.

Preventing errors on future refreshes

Prevention starts with a clear schema. Set types intentionally rather than relying on automatic inference, especially when source data changes over time. If a source column is supposed to contain whole numbers, ask the data owner to enforce that rule or provide a separate exception process. If it is an identifier, specify text consistently at the source and in Power Query.

Test transformations against edge cases as well as typical rows. Include the largest plausible value, the smallest value, blanks, negative values if allowed, and any known text exceptions. For calculations, check whether intermediate results can be larger than the final result; a conversion applied before a calculation can fail even if the final business value would otherwise be acceptable.

Finally, treat refresh as a recurring test. A query that worked last month may encounter a new category, longer code, or unexpectedly large amount this month. Review error rows after changes to source systems, file layouts, or locale settings, and keep a record of intentional type choices so another editor does not reintroduce automatic conversion.

Power Query overflow errors: common questions

Why does Power Query show an overflow when Excel displays the value?
Excel cells and Power Query transformations do not necessarily use the same type or conversion path. A source value may display as text in a worksheet but fail when Power Query converts it to a bounded numeric type. Check the value before the failing step and the type that step applies.

Should I change the whole column to Decimal Number?
Only if decimal arithmetic fits the meaning and precision requirements of the data. Decimal Number is floating point, and it is not an unlimited-precision replacement for every numeric type. For codes, use text; for fixed-precision amounts, assess Fixed Decimal Number and the source’s precision.

Does replacing errors with null fix the underlying problem?
No. It changes how the query handles a failed conversion but does not make the source value valid. Use that approach only when discarding the failed value is acceptable and you can still identify and audit the affected rows.

Why does refresh fail when the preview looks fine?
The preview may not include every source row, and the source may have changed since the preview was generated. A later row can contain a value that fails the same type conversion. Refresh, inspect the full source, and test the query against exceptional values.

Are the steps different in Excel for Mac?
The diagnosis is the same: find the first failing transformation, inspect the value, and correct the type or operation. Power Query connectors and editor features can vary by Excel for Mac version, so the exact menus may differ from the Windows paths described above.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.