Sorting table in Power Query produces different results to sorting same table in Excel

[GregT57]

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

[#2]

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.

[#3]

@Jan Karel Pieterse 

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.

[#4]

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