Sorting of values

[avidthinker]

Hi,

I have a spreadsheet that contains the values below.

Ver

5.29.1

5.29.1

5.29.1

8.03

8.02

8.02

The above is correctly sorted in Excel.

When I create a dataflow, it incorrectly sorts it as 8.03 being the highest value, appreciate any suggestions.

I noticed the values above some are left justified (5.29.1), some are right justified (8.03), would we know the reason why?

[slorin]

Hi @avidthinker 

Maybe

= Table.Sort(Source, (x,y) => Value.Compare(Text.From(x[Ver]), Text.From(y[Ver])))

Stéphane

[ZhangKun]

In Excel, Power BI or other similar products, numbers (or dates and times, etc.) are on the right and text is on the left. This common sense should be remembered by every user, it is very important.

In Excel, text is larger than numbers, even empty text. The same is true in Power Query (sorting only).

Note: These are just experiences, the Power Query documentation does not explicitly state that text is greater than numbers, so it is an undefined behavior and should not be used in production environments.

A good habit is that the data type of a column should be consistent, so in most cases, users will set the column type to text (in this case). At this time, the text will be compared from left to right character by character, such as the first character "8" is larger than "5", and so on, so 8.03 is the maximum value.

However, if you do not convert the type, it is very likely that you will get a different result from Excel.

[v-shamiliv]

Hi @avidthinker 

Thank you for reaching out to the Microsoft Fabric Community Forum.

It looks like the sorting issue may be caused by mixed data types in your column. Some values (e.g., "5.29.1") are likely stored as text, while others (e.g., "8.03") are stored as numbers. This would explain why "8.03" is treated as larger than "5.29.1" when sorted.

Could you please try changing the column’s data type to text in your Dataflow and check if the sorting issue persists?
Thank you.

[avidthinker]

Hi @v-shamilv,

Thanks for the suggestion, the tricky part in some cases 8.03 is the latest and in some cases 5.29.1 is the latest version.

I tried a mechanism where I look at the length of the Ver field, if its over 4 digits (using a filter) as for example 5.29.1, it works and shows 5.29.1 is the latest after a sort.

For another spreadsheet 8.03 is the latest and when I apply this the above methodology and we now filter 8.03 (4 digit length excluded) it's not the latest.

What makes it challenging the Firmware Build XXXXXXXXX adjacent column has no naming scheme which denotes the latest via alphabetical naming, think I may need another point of reference.

[v-shamiliv]

Hi @avidthinker 

Since version formats vary, sorting by length alone may not be reliable. A better approach is to split the "Ver" column into major, minor, and patch components using the . delimiter. Convert these to numbers and sort by major > minor > patch in descending order.

If a version has only two parts (e.g., "8.03"), assume a missing patch version as 0 (i.e., treat "8.03" as "8.3.0"). This should ensure correct sorting.
If this solution helps, please consider giving us Kudos and accepting it as the solution so that it may assist other members in the community
Thank you.

[Akash_Varuna]

HI @avidthinker The issue is beacuse some of the values are traeted as text i guess in your case the one with 2 decimal points be treating as text and the other as number so the text is sorted alpahbetically and the other is sorted numerically so to overcome this you cold conevert evrything to text Like Text.From and then padd the other values to match them and sort accordingly i guess