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?

[avidthinker]

Hi @Akash_Varuna,

Thanks for the tip, ideally I should have a baseline to compare to.

The data field is versions of software released, with a baseline I can do a check on x.xx (or x.x) which I know if the latest and to then filter on the latest.

The latest version baseline I do not have and I need to go on the Ver field which is tricky.

Sometimes x.x (stored as decimal) is the latest, sometimes x.xx (stored as text) is the latest.
Trying to figure out a mechanism that works in all scenarios.

@v-shamiliv I am unable to arrive at a definitive suggestion to test to mark as a solution, please bear with me to see what the community suggests whilst I also pursue a solution.

Thank you

[v-shamiliv]

Hi @avidthinker 
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[v-shamiliv]

Hi @avidthinker 
I wanted to check if you had the opportunity to review the information provided. Please feel free to

reach us if you have any further questions. If  responses posted has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-shamiliv]

Hi @avidthinker 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[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