Feb 22 2017
07:08 AM
- last edited on
Jul 12 2019
10:42 AM
by
TechCommunityAP
Feb 22 2017
07:08 AM
- last edited on
Jul 12 2019
10:42 AM
by
TechCommunityAP
Hello all, I am new to these forums so forgive if I am doing anything wrong.
I have come across a problem with Power Query in Excel 2013. I am importing from a Table in a different Excel workbook, I have removed some columns and i then want to sort the data by each of the four coulumns in turn.
Ihave never had an issue with this in the past and it was always quite simple, in fact in the query editor it appears to sort OK.
Yet when i select "Close and Load" when the table updates it is not sorted at all. The columns to be sorted are text columns.
Any ideas what to look for?
Feb 23 2017 03:39 AM
Hi Peter,
That could be bit abstract without knowing the details of you model, however,
- on PQ site try to isolate your table in memory using 'Add this data to Data Model' in Load To options; or by using Table.Buffer() in M script;
- on Excel site right click on your table and in Table->External Data Properties be sure options are checked as you need, especially Preserve column sort/filter/layout
Jan 29 2018 08:41 AM
Hi Sergei,
Thanks a lot for your comments!
I have tha same issue of Peter. The sorting look correct in Query Editor Preview, however when I Load to table the sorting looks completely different
May 07 2018 08:13 PM
I noticed that sorting follows the ASCII codes, which is different from Excel's sorting behavior. Does it explain the unexpected sorting result?
Aug 14 2020 06:22 AM
@Peter Downer 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 08:44 AM
To sort correctly you need to fox the table in memory. IMHO, Load to data model is not the optimal way for that.
Aug 14 2020 10:13 AM
Aug 19 2020 12:28 PM
You may fix in memory by using Table.Buffer(Table.Sort... or by adding Index column right before sorting and remove it after that if not needed.
Aug 19 2020 12:32 PM
Sep 02 2020 03:55 PM