Excel 2016 Data Model trims trailing spaces

Question

In Excel 2016, after creating a query with Get & Transform, when I'm in the Query Editor, done editing the query, and click "Close & Load" > "Close & Load To", I get the "Import Data" dialog as expected. On that dialog, I check the "Add this data to the Data Model" checkbox to add the data to the data model, and click OK. All seems well so far.

But when I inspect the data in the worksheet table it just created, items that had trailing spaces no longer have those trailing spaces! It doesn't delete leading spaces, nor internal spaces. It just deletes trailing spaces.

When I go back to the query, the trailing spaces are still there, even in the very last step of the transform (to verify that, I right-click any one of them in the Query Editor, choose Copy, and then paste into Notepad, where I see that the trailing spaces are included when I paste). I see no indication anywhere that I've asked it to remove trailing spaces. I see no indication that it's going to do so. But in the table that query loads to, those trailing spaces are gone (I see that by selecting one of the cells in the worksheet table and inspecting the item in Excel's formula bar).

If I do everything the same except just don't check the checkbox to add it to the Data Model, the problem doesn't happen -- the trailing spaces are still there in the worksheet table it loads to. But I want this data in the Data Model.

I need those trailing spaces, and I want this data in the Data Model. How do I make it stop removing the trailing spaces?

UPDATE:

Although I originally saw this issue when querying on a SQL Server database, I've reproduced the problem using only Excel, creating a small sample Excel workbook and querying on it. Here are the complete repro steps using only Excel:

1. In Excel 2016, create a new workbook.
2. In cell A1, enter "MyField" (without quotes).
3. In cell A2, enter "  abc  123  " (without quotes) (that's two spaces, then "abc", then two more spaces, then "123", then two more spaces, for a total of 6 spaces.
4. Save and close the workbook.
5. Create another new workbook in Excel 2016.
6. Choose Data tab > Get & Transform section > New Query > From File > From Workbook.
7. Browse to the workbook you had just created and click Import. That brings up the Navigator dialog.
8. In the Navigator dialog, on the left-hand side, select the worksheet name. You'll see your data appear on the right. On the bottom of the dialog, click Edit. That brings up the Query Editor.
9. In the Query Editor, choose Home tab > Transform section > Use First Row As Headers. That promotes the cell with MyField from data row to header.
10. Next, verify that the trailing spaces are still there in the "  abc  123  ". Right-click that data cell and choose Copy. Then open Notepad and choose Paste. Result: all 6 spaces are there.
11. Back in the Query Editor, choose Home tab > Close section > Close & Load > Close & Load To. That dismisses the Query Editor and brings up the Load To dialog.
12. In the Load To dialog, check the "Add this data to the Data Model" checkbox, and click Load. That loads the query result to a worksheet.
13. In the query result on the worksheet, select the cell with the "  abc  123  ". Now click in Excel's formula bar. Note that the last two spaces are gone! It has become "  abc  123". No trailing spaces.
14. On the right-hand side, right-click the query and choose Edit. That brings up the Query Editor. Verify that the trailing spaces are still there using the method above. When done. click Close & Load.
15. After re-loading in the last step, verify that in the worksheet, those two trailing spaces are NOT there, by inspecting it again in the formula bar.
16. So the trailing spaces are there in the query, but not in the query result on the worksheet. Now let's see what happens when we DON'T add the query to the Data Model:
17. Repeat steps 6-12 above to create another, identical query on the same workbook. Except when you get to the part about checking the "Add this data to the Data Model" checkbox on the Load To dialog, DON'T check it. Leave it unchecked.
18. Then repeat step 13 to check the query result on the worksheet to see if the trailing spaces are there. Result: this time the trailing spaces are still there! The only difference is that this time you did NOT check the "Add this data to the Data Model" checkbox on the Load To dialog. If you check that checkbox, you lose trailing spaces in the query result. If you don't check it, you keep the trailing spaces.

Answer 1

Hi Dann,

It's Microsoft Office 365 ProPlus, version 1704, Build 8067.2020.

This happened in a specific workbook, and I reproduced the problem in a new workbook.

The source of the query is a SQL Server database.

I downloaded, installed, and ran Office Configuration Analyzer Tool 2.2. It didn't show any configuration problems.

Running in safe mode will have to wait until I'm back at work tomorrow; I don't have access to the database from home.

I can probably work out repro steps that do not involve SQL Server. I'll work on that and get back to you.

UPDATE:

I reproduced the problem using only Excel, making a small Excel file to query on. I added complete repro steps to my original post. I think anyone will get the same result if they just take a couple minutes to walk through those steps. 

I also tried Safe Mode as requested, querying on the Excel file. No difference in Safe Mode.

Answer 2

Hello Greg,

We would like to know more about what happened. Kindly answer the following questions:

• Are you using Office for Home, Personal, or For Business?
• Does this concern happen to a specific Workbook?
• Where is the source for the query located?

While you are on it, run the Microsoft Office Configuration Analyzer tool to check for any configuration changes that might cause this concern.

Also, run Excel in safe mode to see if you will encounter the same concern.

Do feel free to reply on this thread if you need further assistance.