Forum Discussion
Sorting table in Power Query produces different results to sorting same table in Excel
I have a power query table that includes as it's last step a sort of rows based on 4 fields 3 of which are numeric data type. The query loads the table back into a worksheet.
In the worksheet i have added some columns that test values in the current row to the previous row in order to set an Id field which is why it is critical that the table is sorted correctly.
I noticed that the results reflected in correct sorting so I then set sort parameters on the table in the worksheet which produced the correct results (by way of sorting the rows). but Excel asked me to accept treating looks like a number as numeric.
What I noticed is that the tables loaded from the query has all the numeric columns (per the query data type) showing as type "General". If I change the columns that are numeric to "Number" I end up with the same results as per the power query by way of incorrectly sorting the rows. I have checked 6 specific records that show problem and cn find nothing strange in their content.
Why do I get such huge difference between the query sorted rows result and the excel table sort?
Thanking you... Greg
- Any chance on getting a file with sample data? I suspect there is a problem with how the data is interpreted in PQ with regards to the data type. You MUST make sure the data type in PQ is correct, otherwise the data type of the end result as it is placed in Excel may be very different from PQ and from what you intend. Needless to say if a column has data type A in PQ and datatype B in Excel, sort is different.
Thanks for your reply and I have already checked that. The file in question is rather large but I will attempt to replicate the situation with a much smaller sample to illustrate my situation.
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?
