Handling “Data overflow” errors during the Load-to-Table phase.

A “Data overflow” error during the Load-to-Table phase means your query processed successfully inside the Power Query Editor, but Excel failed when attempting to write the final dataset into a worksheet grid. This occurs when the data exceeds the worksheet’s maximum row or column boundaries, or when individual values exceed Excel’s numeric or character limits.

Quick Answer

If your query produces more than 1,048,576 rows, change the load destination: right-click the query in the Queries & Connections pane, select Load To…, choose Only Create Connection, and check Add this data to the Data Model. For numeric overflow, cast large integer columns to the Text data type before loading.

---

Why the Error Appears at the Final Step

Power Query operates as an independent, streaming data transformation engine. It can process tens of millions of rows in memory without difficulty.

However, when you click Close & Load, Power Query attempts to materialize the output into an Excel worksheet table (ListObject). At that moment, Excel enforces the physical constraints of the worksheet grid:

• Row Limit: Exactly 1,048,576 rows.
• Column Limit: Exactly 16,384 columns (Column XFD).
• Cell Text Limit: Exactly 32,767 characters per cell.
• Numeric Limits: Numbers exceeding 15 digits of precision or values larger than $1.7976931348623158 $\times 10^{308}$.

If any record violates these worksheet specifications, the query engine aborts the load and displays a data overflow warning.

---

Diagnostic Steps and Solutions

               ["Data overflow" Error on Load]
                              │
                              ▼
           [Did the dataset exceed 1,048,576 rows?]
                              │
              ┌───────────────┴───────────────┐
             YES                              NO
              │                               │
              ▼                               ▼
    [Load to Data Model             [Check Column Values]
    or Create Connection]                     │
                               ┌──────────────┴──────────────┐
                               ▼                             ▼
                      [Integer > 15 digits]        [Text > 32k characters]
                               │                             │
                               ▼                             ▼
                      Change type to Text          Truncate string with
                      in Power Query               Text.Start(..., 32767)

1. Bypassing the 1,048,576 Row Ceiling

If your dataset contains millions of rows (such as transactional logs or web telemetry), it cannot physically fit onto a standard sheet.

1. Open your workbook and display the Queries & Connections pane (Data > Queries & Connections).
2. Right-click the failing query and select Load To….
3. In the dialog, select Only Create Connection.
4. Check the box labeled Add this data to the Data Model.
5. Click OK.

┌────────────────────────────────────────────────────────┐
│  Import Data                                           │
├────────────────────────────────────────────────────────┤
│  Select how you want to view this data:               │
│  ( ) Table                                             │
│  ( ) PivotTable Report                                 │
│  (•) Only Create Connection                            │
│                                                        │
│  [✓] Add this data to the Data Model ◄──────────────── │
│                                                        │
│  [ OK ]                               [ Cancel ]       │
└────────────────────────────────────────────────────────┘

The data will stream directly into the Power Pivot xVelocity engine (which can store hundreds of millions of rows using columnar compression). You can then analyze the data by building a PivotTable directly from the Data Model without ever writing raw rows onto the worksheet grid.

For broader system constraints when loading high-volume datasets, see Troubleshooting “Data Overflow” in Excel when SQL result sets exceed 1,048,576 rows and Fixing “There isn’t enough memory to complete this action” in large datasets.

2. Investigating Accidental Cartesian Product Joins

If your source files normally contain only a few thousand rows, but the query suddenly triggers an overflow, you likely experienced an accidental Cartesian expansion during a merge operation.

• The Cause: When merging two tables on a key that contains duplicate values in both tables, Power Query generates an $M \times N$ row expansion. A table with 2,000 duplicate keys merged against another table with 2,000 duplicate keys produces 4,000,000 rows.
• The Fix: Open the query in the Power Query Editor. Inspect your merge steps. Ensure you remove duplicates from the lookup table’s key column before the merge, or group rows to ensure the join key is distinct.
• To review join mechanics and unintended row expansions, see Expression.Error: Multiple matches found in a Join (Cartesian product trap).

3. Resolving Integer and Scientific Overflow

Worksheet cells use the IEEE 754 floating-point specification, which supports a maximum of 15 digits of precision. If an input column contains 16-to-19-digit identifiers, such as credit card numbers, tracking IDs, or 64-bit database integers (BIGINT), loading them as numbers can cause overflow or precision truncation.

1. Open the query in the Power Query Editor.
2. Identify any columns containing large numeric IDs.
3. Click the data type indicator in the column header (e.g., 123).
4. Change the data type to Text.
5. When prompted to replace the existing step or add a new step, choose Replace current.
6. Close and Load. Text values bypass floating-point limits and load safely onto the sheet.

For handling integer boundary errors inside transformations, see “The value was too large or too small for an Int64”: Fixing Integer Overflows.

4. Handling Text Strings Longer than 32,767 Characters

If a column imports massive JSON blobs, XML strings, or raw base64 data, an overflow error triggers if any single record exceeds 32,767 characters.

• The Fix: Add a transformation to truncate text columns to the Excel limit: = Table.TransformColumns(#"PreviousStep", {{"LogDetails", each Text.Start(_, 32767), type text}})
• For more detail on identifying and cleaning oversized text columns, see Troubleshooting “Truncated Data” errors in long text columns (>32k characters).