Power Query Preserve column sort/filter/layout

%3CLINGO-SUB%20id%3D%22lingo-sub-106521%22%20slang%3D%22en-US%22%3EPower%20Query%20Preserve%20column%20sort%2Ffilter%2Flayout%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-106521%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20Excel%202016%2C%20Version%201705%20(Build%208201.2193)%2C%20if%20I%20have%20a%20query%20from%20Get%20%26amp%3B%20Transform%20that%20is%20returned%20to%20the%20worksheet%2C%20it%20reorders%20the%20columns%20to%20match%20what%20is%20in%20the%20query%20editor%20every%20time%20I%20refresh%20-%20even%20if%20I%20check%20the%20box%20to%20%22Preserve%20column%20sort%2Ffilter%2Flayout%22%20in%20External%20Data%20Properties.%26nbsp%3B%20In%20previous%20versions%2C%20checking%20this%20box%20would%20maintain%20whatever%20column%20order%20is%20in%20the%20worksheet%20and%20new%20columns%20would%20be%20added%20at%20the%20end.%26nbsp%3B%20Is%26nbsp%3Bthere%20a%20new%20setting%20somewhere%20that%20I%20can%20set%20so%20it%20doesn't%20change%20the%20column%20order%20each%20refresh%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-106521%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EGet%20%26amp%3B%20Transform%20Data%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-309226%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Preserve%20column%20sort%2Ffilter%2Flayout%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-309226%22%20slang%3D%22en-US%22%3EJust%20wanted%20to%20follow%20up%20on%20this%20query.%20Had%20anyone%20had%20any%20luck%20with%20resolving%20this%3F%20I%E2%80%99ve%20tried%20all%20the%20external%20data%20options%20and%20it%20seems%20like%20the%20button%20for%20%E2%80%9Cpreserve%20column%20sort%2Flayout%E2%80%9D%20doesn%E2%80%99t%20do%20anything%20in%20Excel%20365.%20It%20worked%20fine%20in%20Excel%202013%20with%20PQ%20addin.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-107078%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Preserve%20column%20sort%2Ffilter%2Flayout%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-107078%22%20slang%3D%22en-US%22%3EHi%20Chris%2C%3CBR%20%2F%3E%3CBR%20%2F%3EI%20hadn't%20noticed%20that%20change%2C%20but%20I%20can%20see%20what%20you%20mean.%20I%20guess%20it%20would%20be%20best%20practice%20to%20sort%20your%20columns%20in%20Power%20Query%20editor%20anyway%20but%20an%20interesting%20change%20of%20how%20things%20work...%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-880301%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Preserve%20column%20sort%2Ffilter%2Flayout%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-880301%22%20slang%3D%22en-US%22%3E%3CP%3EI%20still%20find%20this%20to%20be%20an%20issue.%20Hopefully%20Microsoft%20will%20address%20this%20issue.%20Weird%20that%20it%20would%20work%20perfectly%20in%202013%20but%20then%20not%20in%20subsequent%20versions.%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F77993%22%20target%3D%22_blank%22%3E%40Chris%20Rankin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1589018%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Preserve%20column%20sort%2Ffilter%2Flayout%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1589018%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F77993%22%20target%3D%22_blank%22%3E%40Chris%20Rankin%3C%2FA%3E%26nbsp%3BI%20know%20it's%20been%20a%20long%20time%20but%20I've%20been%20looking%20for%20an%20answer%20to%20this%20problem%20for%20a%20long%20time%20and%20found%20it%20on%20an%20even%20older%20thread%3A%20unchecking%20the%20%22Add%20this%20to%20the%20Data%20Model%22%20option%20in%20the%20%22Load%20To...%22%20settings%2C%20makes%20the%20data%20sorting%20in%20the%20table%20the%20same%20as%20the%20one%20seen%20in%20the%20query%20preview.%3CBR%20%2F%3E%3CBR%20%2F%3ESource%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fchandoo.org%2Fforum%2Fthreads%2Fpower-query-sorting.23455%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fchandoo.org%2Fforum%2Fthreads%2Fpower-query-sorting.23455%2F%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1589070%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Preserve%20column%20sort%2Ffilter%2Flayout%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1589070%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F759683%22%20target%3D%22_blank%22%3E%40claudiapeitzp%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20wrap%20previous%20step%20with%20Table.Buffer%2C%20or%2C%20alternatively%2C%20add%20Index%20before%20sorting%20and%20remove%20after%20that.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1589485%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Preserve%20column%20sort%2Ffilter%2Flayout%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1589485%22%20slang%3D%22en-US%22%3EIn%20my%20case%20(and%20the%20other%20users%E2%80%99%20in%20the%20source%20forum%20I%20posted%20too)%2C%20adding%20the%20row%20index%20and%20sorting%20by%20it%20would%20still%20load%20the%20query%20to%20the%20table%20in%20an%20differently%20sorted%20way.%20This%20means%20I%20would%20have%20to%20sort%20the%20index%20column%20in%20excel%20every%20time%20I%20refreshed%20the%20table%2C%20which%20is%20not%20a%20practical%20solution%20either.%20I%20believe%20this%20is%20some%20kind%20of%20bug%2C%20but%20I%E2%80%99ve%20only%20been%20working%20with%20power%20query%20in%20excel%20for%20a%20couple%20of%20months.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1589540%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Preserve%20column%20sort%2Ffilter%2Flayout%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1589540%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F759683%22%20target%3D%22_blank%22%3E%40claudiapeitzp%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDid%20you%20try%20Table.Buffer()%20%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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?

7 Replies
Hi Chris,

I hadn't noticed that change, but I can see what you mean. I guess it would be best practice to sort your columns in Power Query editor anyway but an interesting change of how things work...
Just wanted to follow up on this query. Had anyone had any luck with resolving this? I’ve tried all the external data options and it seems like the button for “preserve column sort/layout” doesn’t do anything in Excel 365. It worked fine in Excel 2013 with PQ addin.

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 

@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/

@claudiapeitzp 

You may wrap previous step with Table.Buffer, or, alternatively, add Index before sorting and remove after that.

In my case (and the other users’ in the source forum I posted too), adding the row index and sorting by it would still load the query to the table in an differently sorted way. This means I would have to sort the index column in excel every time I refreshed the table, which is not a practical solution either. I believe this is some kind of bug, but I’ve only been working with power query in excel for a couple of months.

@claudiapeitzp 

Did you try Table.Buffer() ?