Truncated data errors occur in Excel when importing text strings that exceed the 32,767-character limit of a single worksheet cell. While Power Query can easily process strings with millions of characters in memory, attempting to output those long strings to a standard Excel worksheet table causes Excel to cut the text off at 32,767 characters or fail with a data load error. This issue commonly appears when importing raw JSON payloads, XML logs, system error logs, or database columns defined as VARCHAR(MAX).
Quick Answer
To prevent text truncation, do not load large text fields directly into an Excel worksheet table. In the Power Query Close & Load To… dialog, select Only Create Connection and check Add this data to the Data Model. Alternatively, split the long text column into segments of 30,000 characters before loading it onto a worksheet.
Understanding the 32,767-Character Grid Limitation
Every version of Microsoft Excel enforces a strict architectural limit on its worksheet grid: a single cell can hold a maximum of 32,767 characters.
When you build a query in Power Query, the preview screen handles long strings without issue because it runs on the Mashup Engine. The problem begins when you click Close & Load to write the query results into an Excel table on a worksheet. At that point, the Excel grid interface takes over:
- Any character past position 32,767 is permanently dropped.
- Downstream lookup formulas, text manipulation functions, and audits will miss the lost text.
- If the load process treats the overflow as a data constraint violation, the query fails with a load phase error. For more details on worksheet load errors, see Handling “Data overflow” errors during the Load-to-Table phase.
To understand how native worksheet formulas behave when they hit this same character ceiling, refer to #CALC! in Text Manipulation: Exceeding character limits (32,767).
Step-by-Step Diagnostic and Resolution Path
Solution 1: Route Data to the Data Model Instead of the Worksheet
The most effective way to store massive text strings without truncation is to bypass worksheet cells entirely. Power Pivot and the Excel Data Model use the VertiPaq engine, which does not share the 32,767-character cell limit.
- In the Power Query Editor, go to the Home tab.
- Click the dropdown arrow under Close & Load and select Close & Load To….
- In the dialog box, select Only Create Connection.
- Check the box labeled Add this data to the Data Model.
- Click OK.
Your complete strings are now preserved inside the workbook’s internal database. You can reference them using DAX or link them in relationships without losing a single character.
Solution 2: Parse Structured Data Inside Power Query First
Long text strings usually enter Excel because an entire structured file, such as a JSON payload, an XML document, or an API response, was imported into a single column. Instead of sending the raw text block to a cell, extract only the fields you need while still inside Power Query:
- For JSON: Right-click the column header and select Transform > JSON. Click the expand icon on the column header to extract specific fields into individual columns.
- For Delimited Logs: Use Split Column > By Delimiter to break the single long row into standard table fields.
Extracting specific attributes drops the overall character count of each cell well below the 32k threshold.
Solution 3: Split Long Text into Multiple Columns
If you must load unstructured text (such as long legal clauses or raw system traces) into a visible worksheet table, split the text into manageable chunks across multiple columns.
- In Power Query, select the long text column.
- Go to the Transform tab and click Split Column > By Number of Characters.
- Set Number of characters to
30000(staying safely below the 32,767 limit). - Under Split, choose Repeatedly.
- Click OK.
Power Query generates the following M code using Splitter.SplitTextByRepeatedLengths:
= Table.SplitColumn(#"Previous Step", "RawLog", Splitter.SplitTextByRepeatedLengths(30000), {"RawLog.1", "RawLog.2", "RawLog.3"})
Each column now stays within the cell character limit, and the full content remains visible across adjacent cells.
Solution 4: Split Long Text into Multiple Rows
If you want to keep your data in a single column rather than creating multiple wide columns, split the text into multiple rows instead:
- Select the column.
- Go to Transform > Split Column > By Number of Characters.
- Set the character count to
30000. - Select Repeatedly.
- Expand Advanced options and select Rows instead of Columns.
- Click OK.
What to Check If Truncation Still Occurs
If your data is still being cut off before reaching 32,000 characters, the truncation is happening before the data reaches Power Query:
- Source Database Settings: If you are importing from an enterprise relational database, check whether the source query or view casts data to a fixed-length string like
VARCHAR(4000)orVARCHAR(8000). For instructions on preserving unlimited database strings during import, see Fixing “Truncated Data” errors when importing SQL VARCHAR(MAX) columns. - Python or Script Truncation: If the text was generated via external Python transformations or custom scripts inside your pipeline, string objects may be truncated at 4,000 or 8,000 characters by default buffer limits. For troubleshooting external script strings, see Handling “Large String” truncations (>32k characters) in Python cell returns.