I was asked to simplify a pair of Excel workbooks that used Power Query. One of the workbook's power queries used a table in the other workbook as a source. That other table was populated by a power query in the other workbook.
I was asked to eliminate the 2nd workbook. So I examined all the logic in the queries and put all the logic in the queries in the final (and now only) workbook.
It worked fine except that in two of the tables, some of the items now have trailing spaces that were not there before.
I found that the SQL Server that is the ultimate source for all this data DOES have the trailing spaces. And the first power query in the old workbook also has the trailing spaces, even at the very last step.
But the table in that workbook that is loaded by that power query does NOT have the trailing spaces. I tried refreshing the query, and I tried redoing it's "Load To" command to load the table. Still the query does have the trailing spaces, while the table it loads does not have them.
In the final, single workbook after eliminating that other workbook, queries are also loaded to tables. But those tables retain the trailing spaces from the queries.
Data types are the same between the old and the new. The columns in question have data type "any" (the data type icons show "ABC123").
Although it would be possible to have a macro in the old workbook automatically run when the table is updated, and that macro could be set up to trim trailing spaces, no such macro is in place in that workbook.
I don't see any logic anywhere that looks to me like it would be trimming those trailing spaces. Any idea how that might be happening?
BTW the old system was set up in 2014, so they were probably using Excel 2013 and the Power Query add-in. I'm using Excel 2016 and Get and Transform.
