Sep 14 2017
12:53 PM
- last edited on
Jul 12 2019
10:51 AM
by
TechCommunityAP
Sep 14 2017
12:53 PM
- last edited on
Jul 12 2019
10:51 AM
by
TechCommunityAP
In Excel 2016, Version 1705 (Build 8201.2193), if I have a query from Get & Transform that is returned to the worksheet, it reorders the columns to match what is in the query editor every time I refresh - even if I check the box to "Preserve column sort/filter/layout" in External Data Properties. In previous versions, checking this box would maintain whatever column order is in the worksheet and new columns would be added at the end. Is there a new setting somewhere that I can set so it doesn't change the column order each refresh?
Sep 16 2017 05:04 AM
Jan 04 2019 05:04 PM
Sep 27 2019 06:52 PM
I still find this to be an issue. Hopefully Microsoft will address this issue. Weird that it would work perfectly in 2013 but then not in subsequent versions. @Chris Rankin
Aug 14 2020 06:26 AM
@Chris Rankin I know it's been a long time but I've been looking for an answer to this problem for a long time and found it on an even older thread: unchecking the "Add this to the Data Model" option in the "Load To..." settings, makes the data sorting in the table the same as the one seen in the query preview.
Source: https://chandoo.org/forum/threads/power-query-sorting.23455/
Aug 14 2020 06:41 AM
You may wrap previous step with Table.Buffer, or, alternatively, add Index before sorting and remove after that.
Aug 14 2020 10:08 AM
Aug 14 2020 10:46 AM
Did you try Table.Buffer() ?