“The value was too large or too small for an Int64”: Fixing Integer Overflows

The error "The value was too large or too small for an Int64" occurs in Power Query when a number exceeds the storage limits of a 64-bit signed integer (Int64.Type). An Int64 whole number must fall between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807. If a data source imports an identifier, tracking code, or calculation that exceeds roughly 9.22 quintillion, such as a 19-digit SIM card number, credit card number, or large database key, Power Query crashes during type casting.

Quick Answer

To fix this error, change the column data type from Whole Number to Text if the value is an identifier (such as an ICCID, barcode, or transaction ID). If the value is a true numeric measurement or currency calculation, change the data type to Decimal Number (type number).

---

Understanding the Int64 Limit in Power Query

When Power Query imports data from CSVs, Excel workbooks, or databases, it frequently inserts an automatic step called Changed Type. During this step, the engine inspects the first 1,000 rows. If it sees only digits, it automatically assigns the Whole Number type (Int64.Type).

The 64-bit integer standard is designed for whole numbers without decimals. Its ceiling is $2^{63}-1$, which is approximately 19 digits long:

• Maximum Positive Value: 9,223,372,036,854,775,807
• Minimum Negative Value: 9,223,372,036,854,775,808

If a single record in your dataset contains a 20-digit number, a 19-digit number starting with a 9 that exceeds the limit, or a scientific notation string that expands beyond these bounds, the transformation engine halts.

To understand how automatic type detection introduces these failures across your queries, review Auditing “Automatic Step Generation” that breaks future refreshes.

---

Step-by-Step Diagnostic and Repair Path

1. Locate the Failing Row

Do not click the blue error link directly, as that inserts an unwanted navigation step.

1. Open the Power Query Editor.
2. Scroll to the column showing Error.
3. Click in the white space inside the cell next to the word Error.
4. Read the detail box at the bottom:
   DataFormat.Error: The value was too large or too small for an Int64.Details: [Value]
5. Note the value displayed in the details. If it has 16 to 20 digits, it was mistakenly classified as an integer.

2. Fix Numeric Identifiers (Change to Text)

Numeric identifiers (such as ICCIDs, account numbers, routing numbers, and parcel IDs) are not used for math. Treating them as integers causes overflow errors and strips leading zeroes.

1. In the Applied Steps pane, select the Changed Type step where the failure occurred.
2. In the formula bar, look for the column reference:
   {"AccountNumber", Int64.Type}
3. Change Int64.Type to type text:
   {"AccountNumber", type text}
4. Press Enter.

Alternatively, delete the Changed Type step altogether, select the column header, click the data type icon, and choose Text.

3. Fix Genuine Calculations (Change to Decimal Number)

If the column represents actual quantities, astronomical figures, or high-volume financial totals, change the data type to Decimal Number (type number).

In Power Query, the Decimal Number type represents an IEEE 754 double-precision floating-point number. It accommodates values up to roughly $1.79 $\times$ $10^{308}$, far beyond the capacity of an Int64.

1. Click the data type icon on the column header.
2. Select Decimal Number.
3. When prompted, choose Replace current step.

---

Preventing Upstream and Database Overflows

If you are pulling data from external databases, integer overflows can occur before Power Query ever sees the data:

• SQL Server BigInt: A SQL BIGINT matches Int64 exactly. If a SQL table uses NUMERIC(38,0) or DECIMAL(38,0) for oversized IDs, importing that column into Power Query without explicit casting causes immediate type mapping failures. For handling high-precision database fields, see Handling “Arithmetic Overflow” when importing BigInt data into Excel.
• Decimal Precision Limits: If you are importing financial data with high decimal precision, converting to standard floating-point numbers can cause subtle rounding discrepancies. Review Fixing “Precision Loss” when importing SQL DECIMAL(38,18) into Excel to preserve exact figures.