Fixing “Scientific Notation” errors (e.g., 1.23E+10) during data import.

Scientific notation errors (such as 1.23457E+10) occur during data import when Excel or Power Query interprets a long sequence of digits, such as a barcode, credit card, tracking number, or account identifier, as a mathematical number rather than text. Because Excel limits numeric precision to 15 significant digits and displays numbers with 12 or more digits in scientific format, importing numeric identifiers as numbers truncates trailing digits to zeroes or converts them into exponential shorthand.

Quick Answer

To fix scientific notation errors, prevent Power Query from auto-detecting types. In the Applied Steps pane, delete the automatically generated Changed Type step. Then, click the column header icon for your ID column and explicitly select Text. When importing CSVs, set Data Type Detection to Do not detect data types.

---

Why Excel Forces Scientific Notation

Excel adheres to the IEEE 754 floating-point standard. This system introduces two distinct failure points for numeric identifiers:

1. Display Conversion (11+ Digits): Any number with 12 or more digits (like 123456789012) is automatically displayed in scientific notation (1.23457E+11) unless explicitly formatted otherwise.
2. Permanent Truncation (15+ Digits): Any numeric value exceeding 15 significant digits permanently loses all digits past the 15th position, replacing them with zeroes. For instance, a 16-digit credit card number 4111222233334444 becomes 4111222233334440. Once this truncation happens in memory, the original digits cannot be recovered.

When you import a CSV, text file, or database export into Power Query, the import engine automatically inspects the first 1,000 rows and adds a Changed Type step that casts these columns to Int64.Type or type number. This step converts your identifiers into floating-point numbers, triggering the scientific notation formatting.

---

Step-by-Step Fixes in Power Query

Step 1: Remove the Automatic “Changed Type” Step

1. Open the Power Query Editor.
2. Look at the Applied Steps list on the right side of the window.
3. Locate the step named Changed Type directly below Source or Promoted Headers.
4. Click the X icon to delete this step.

Deleting this step leaves all columns typed as type any or raw text, preserving the full string of digits without scientific notation. For details on managing these automatic steps, see Auditing “Automatic Step Generation” that breaks future refreshes.

Step 2: Convert the Identifier Column Directly to Text

1. Click the data type symbol (such as 123 or 1.2) on the left side of the column header.
2. Select Text from the dropdown menu.
3. If prompted with “Change Column Type: Replace current step or Add new step?”, select Replace current step.

By defining the column as Text at the earliest opportunity, Power Query reads the characters verbatim and leaves no opportunity for scientific notation conversion.

Step 3: Prevent Automatic Type Detection on CSV Imports

If you frequently import CSV files containing identifiers:

1. When the initial import preview dialog opens, locate the Data Type Detection dropdown menu at the top.
2. Change the selection from Based on first 200 rows to Do not detect data types.
3. Click Transform Data.
4. Manually assign data types only to columns that actually require mathematical operations.

---

How to Recover Numbers Already Displayed as Scientific Notation

If the source file already contains scientific notation (for example, another user saved an Excel sheet where numbers were converted to 1.23457E+10), restoring the original values depends on whether the numbers exceeded 15 digits.

Case A: 15 Digits or Fewer (Digits Preserved, Shorthand Displayed)

If the original number had 15 or fewer digits, no data was lost; Excel is merely rendering it in scientific format. You can convert it back to a full string:

In Power Query, add a Custom Column using Number.ToText:

= Number.ToText([IdentifierColumn], "D", "en-US")

The format specifier "D" (Decimal) forces Power Query to render the underlying numeric value as a complete integer string without scientific shorthand.

Case B: Greater Than 15 Digits (Precision Permanently Destroyed)

If a 16-digit card number or 18-digit tracking ID was opened, modified, and saved in standard Excel as a regular number, the digits past the 15th position were permanently converted to zeroes (...000).

No formula or Power Query transformation can guess the truncated digits. To resolve this:

• Re-export the original file from the source database.
• Ensure the database extraction surrounds the ID with text delimiters (quotes) or prefixes the cell with an apostrophe (').
• For SQL-based ingestion pipelines, ensure your query casts the column as VARCHAR or NVARCHAR before loading. Review Troubleshooting “Scientific Notation” conversion of SQL ID strings for proper SQL-to-Excel mapping techniques.

For similar formatting traps involving numeric codes like Social Security numbers, see SSN Formatting: Why leading zeros disappear and how to fix “Scientific Notation” in IDs.