Forum Discussion
Sorting table in Power Query produces different results to sorting same table in Excel
GregT57 I've been looking for an answer to this problem for a long time and found it on an old thread deep down on my googling: 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/
- Great answer that helped me get to the root of it. I cannot remove my connections from the data model, but it helped me isolate the issue comes from the data model.
There is a data model in excel that may have different settings that override Power Query sorting. Under Data > Data Tools > Manage Data Model.
In here, I saw the trouble come had a weird sort applied, I set it as desired, and that did it for me!
Understanding the data model can be really helpful, especially when working with dates!Your post filled me with hope! But when I followed your directions to the data model in excel there were no sorts applied to any of the columns so I'm still stuck with my table in power query and in excel in different orders.
Can you see if I'm missing something or do you have any other suggestions?
In External data properties setting Preserve column sort/filter could be ON
If data type is Any and data is loaded to data model, it will be considered as text and return to Excel as text. Iven if values are looks like numbers. With the Power Query will sort them as numbers and Excel as texts.
Perhaps something else, it's better to have small sample file which illustrates an issue for this concrete case.
