Power Query Problems

Fix Trailing Spaces Removed from Excel Power Query Data Model Results

John WilsonJohn Wilson Nov 20, 2026 869 views

Question details

Preserve trailing spaces in text loaded from Excel Power Query into the Data Model.

Fix Trailing Spaces Removed from Excel Power Query Data Model Results
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.
Before you start

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.

Solution 1Recommended

Update Excel and test the query in a new workbook

Check whether the behavior persists in an updated installation and a separate workbook.

1
Install Office updates

In Excel, open File > Account > Update Options > Update Now, if that option is available for your installation.

2
Create a test query

Open a new workbook and use Data > Get Data to create a query from a small sample containing text with and without trailing spaces.

3
Compare load destinations

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.

Updates may not change the result: The behavior was also reproduced in a new workbook and in Excel Safe Mode in the reported case. If it persists, use a marker or a worksheet table as a workaround.
Free Microsoft Office alternative

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.

Open and edit Microsoft Excel XLSX and XLS filesReview text values and lengths in spreadsheet cellsUse a familiar spreadsheet interface for everyday workMove common spreadsheet files without recreating them
microsoft office alternative - wps office

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.