How to Fix Power Query Truncating Text at 255 Characters
Question details
The user needs a solution to prevent Power Query from cutting off long text strings at 255 characters when extracting data from another file.
- Product
- Microsoft Excel (Power Query)
- Device & OS
- not provided
- Scenario
- Using Power Query in a workbook to extract data from a table stored in another workbook that is currently open.
- Observed behavior
- Long text fields load correctly when copied and pasted manually, but are truncated to exactly 255 characters when loaded via Power Query from the external open workbook.
Ensure the source workbook containing your data is completely saved and closed before refreshing your query, as leaving it open forces Excel to use a legacy data driver that causes this limitation.
Format Data as a Structured Table and Close Source File
Converting the source range to an official Excel Table and explicitly setting the column type helps the query engine accurately interpret string lengths.
When querying another workbook that is currently open, Excel relies on legacy background drivers that sample the first few rows of data. If the driver does not immediately detect long text, or if the file is locked for editing, it applies a default 255-character limit to the column.
Open the source workbook, highlight your entire data range, and press Ctrl+T to convert it into a structured Excel Table.
Select the specific column containing the long text, right-click, choose 'Format Cells', and set the category to 'Text'.
Save your changes and completely close the source workbook so it is no longer locked.
In your destination workbook, open the Power Query Editor, verify that the column data type is explicitly set to 'Text', and click 'Close & Load' to refresh the data without truncation.
Export Source Data to a CSV File
Bypassing the Excel workbook connection entirely avoids the driver limitations that cause the 255-character cutoff.
Experience Smooth Data Handling with WPS Office
Tired of encountering complex query limitations and unexpected data truncation? WPS Office provides a highly compatible, fast, and free alternative to Microsoft Office. Manage your spreadsheets seamlessly and enjoy robust data processing without the heavy subscription fees.
- 1. Download WPS Office: Visit the official WPS website to download and install the free software suite.
- 2. Open your files: Launch WPS Spreadsheets and directly open your existing Excel workbooks with zero formatting loss.
- 3. Process your data: Use built-in data handling tools to filter, sort, and analyze your text-heavy datasets without unexpected truncation.
Frequently Asked Questions
Why does Excel Power Query limit text to exactly 255 characters?
This issue is caused by legacy OLE DB or ODBC drivers used by Excel in the background. If the source file is open, the driver falls back to an older compatibility mode that caps string fields at 255 characters to save memory, especially if the first few rows sampled don't contain longer text.
Does closing the source workbook always prevent text truncation?
In most cases, yes. When the source Excel file is completely closed, Power Query can use a different, more modern extraction method that bypasses the 255-character restriction imposed on open files.
How can I force Power Query to recognize long text strings?
You can help the query engine by organizing your source data into a structured Excel Table (Ctrl+T) and explicitly defining the column's data type as 'Text' within the Power Query Editor before applying the 'Close & Load' step.
Where can I get advanced help for complex Power Query errors?
Because Power BI shares the exact same Power Query engine as Microsoft Excel, the Microsoft Power BI Community forums are highly recommended for advanced troubleshooting and specialized query issues.
