Fix Trailing Spaces Removed from Excel Power Query Data Model Results
Question details
Preserve trailing spaces in text loaded from Excel Power Query into the Data Model.

- Product
- Microsoft Excel Power Query and Data Model
- Device & OS
- not provided
- Scenario
- A user loads a Power Query containing text with trailing spaces into the Excel Data Model and compares the result with a worksheet table.
- Observed behavior
- Trailing spaces appear to be preserved in Power Query and worksheet table results but removed in Data Model results.
Identify a sample text value with trailing spaces and check its length in Power Query and in a worksheet table. Note whether downstream calculations or relationships depend on the exact text value.
Update Excel and test the query in a new workbook
Check whether the behavior persists in an updated installation and a separate workbook.
In Excel, open File > Account > Update Options > Update Now, if that option is available for your installation.
Open a new workbook and use Data > Get Data to create a query from a small sample containing text with and without trailing spaces.
Use Home > Close & Load To in Power Query to load the sample to a worksheet table, then test the same query in the Data Model. Compare the values and their lengths in the resulting outputs.
Append a marker before loading text into the Data Model
Use a deliberate marker when trailing spaces must survive the Data Model load.
Load the query into a worksheet table instead
Avoid the Data Model when a standard worksheet table meets your reporting needs.
Work with Excel-compatible spreadsheets in WPS Office
WPS Office is a free, lightweight Office alternative with a familiar interface and support for common Excel formats, making it easy to move spreadsheet work to WPS. It does not fix Excel Power Query or Data Model behavior.

Frequently Asked Questions
Why do trailing spaces appear in a worksheet table but not Data Model results?
The same query can behave differently depending on its load destination. Compare a sample value and its length after loading to a worksheet table and to the Data Model to confirm where the difference appears.
How can I check whether a cell still has trailing spaces?
Use a worksheet formula such as =LEN(A2) and compare the result with the expected number of characters. A longer result than the visible text can indicate trailing spaces.
Will adding a marker affect Data Model relationships?
It can. A marked value is different from its unmarked original, so encode matching keys consistently and test relationships and filters after loading.
Can I remove the marker without removing the trailing spaces?
Yes. Remove only the final marker character in the final output. For a marked value in cell A2, use =LEFT(A2,LEN(A2)-1); avoid TRIM because it can remove spaces you want to keep.




