cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pchilton
Frequent Visitor

Sorting Version Number with multiple decimal points formatted as text

 

 

I am trying to sort Version numbers that will be displayed on a graph and I am running into the infamous sorting where 2.0.10.0 does not come after 2.0.9.0 because it looks at 1 vs 9 (See Below)  I cannot convert to numeric because of the number of decimals.  Anyone have any suggestions?  I have thought of creating a new column and parsing through to 3rd set and pad with 0 for values with a len of 1 for sorting purposes, but making that happen has been tougher than my beginner skills have conquered.  I originally made a static table with versions and sort value but that had to be edited manually with each version change making it a pain for reporting.   Need something dynamic.

 

Version.JPG

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User III
Super User III

Re: Sorting Version Number with multiple decimal points formatted as text

Hi @pchilton,

 

Do the following steps on advance query editor:

 

  • Add a custom column based on Version number
  • Split the new column by Delimiter "."
  • Add a new custom column with the following code:

 

Text.PadStart ([Valid.1], 2, "0") & "." &
Text.PadStart ([Valid.2], 2, "0") & "." &
Text.PadStart ([Valid.3], 2, "0") & "." &
Text.PadStart ([Valid.4], 2, "0")
  • Sort by new column
  • Delete Columns created with split delimiter

See below the M Code for a Query editor so you can replicate and look at what I did.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtIz0DM00DNQitWBcIz1DA2ROEZGcI4JkioTPWM42xIkHgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Version = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Version", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Valid", each [Version]),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "Valid", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Valid.1", "Valid.2", "Valid.3", "Valid.4"}),
    #"Added Custom1" = Table.AddColumn(#"Split Column by Delimiter", "Sort_Version", each Text.PadStart ([Valid.1], 2, "0") & "." &
Text.PadStart ([Valid.2], 2, "0") & "." &
Text.PadStart ([Valid.3], 2, "0") & "." &
Text.PadStart ([Valid.4], 2, "0")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Valid.1", "Valid.2", "Valid.3", "Valid.4"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Sort_Version", Order.Ascending}})
in
    #"Sorted Rows"

 

I assumed that you only go to 2 numbers in each part of the Version that why the Padding of the text for all columns is 2.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Check out my blog: Power BI em Português


View solution in original post

Super User III
Super User III

Re: Sorting Version Number with multiple decimal points formatted as text

Hi @pchilton,

 

On the desktop choose the original column Version and the:

 

  • Modeling
  • Sort By Column

In this option select the new Version column to sort the first one by this new one as you can see below the values are sort by default by the sort column.

 

sort.gif

 

Regards,

MFelix

 

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Check out my blog: Power BI em Português


View solution in original post

10 REPLIES 10
Super User III
Super User III

Re: Sorting Version Number with multiple decimal points formatted as text

Hi @pchilton,

 

Do the following steps on advance query editor:

 

  • Add a custom column based on Version number
  • Split the new column by Delimiter "."
  • Add a new custom column with the following code:

 

Text.PadStart ([Valid.1], 2, "0") & "." &
Text.PadStart ([Valid.2], 2, "0") & "." &
Text.PadStart ([Valid.3], 2, "0") & "." &
Text.PadStart ([Valid.4], 2, "0")
  • Sort by new column
  • Delete Columns created with split delimiter

See below the M Code for a Query editor so you can replicate and look at what I did.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtIz0DM00DNQitWBcIz1DA2ROEZGcI4JkioTPWM42xIkHgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Version = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Version", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Valid", each [Version]),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "Valid", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Valid.1", "Valid.2", "Valid.3", "Valid.4"}),
    #"Added Custom1" = Table.AddColumn(#"Split Column by Delimiter", "Sort_Version", each Text.PadStart ([Valid.1], 2, "0") & "." &
Text.PadStart ([Valid.2], 2, "0") & "." &
Text.PadStart ([Valid.3], 2, "0") & "." &
Text.PadStart ([Valid.4], 2, "0")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Valid.1", "Valid.2", "Valid.3", "Valid.4"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Sort_Version", Order.Ascending}})
in
    #"Sorted Rows"

 

I assumed that you only go to 2 numbers in each part of the Version that why the Padding of the text for all columns is 2.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Check out my blog: Power BI em Português


View solution in original post

pchilton
Frequent Visitor

Re: Sorting Version Number with multiple decimal points formatted as text

Thank you, that worked!  I appreciate your assistance.

 

Now I am working on displaying the original format in the sorted order in the visual.  Smiley Happy  Always something to learn.

Super User III
Super User III

Re: Sorting Version Number with multiple decimal points formatted as text

Hi @pchilton,

 

On the desktop choose the original column Version and the:

 

  • Modeling
  • Sort By Column

In this option select the new Version column to sort the first one by this new one as you can see below the values are sort by default by the sort column.

 

sort.gif

 

Regards,

MFelix

 

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Check out my blog: Power BI em Português


View solution in original post

pchilton
Frequent Visitor

Re: Sorting Version Number with multiple decimal points formatted as text

Thank you again! Problem solved.

KyleB
Regular Visitor

Re: Sorting Version Number with multiple decimal points formatted as text

I have a Categorical Line Chart where the Axis is a version number, the legend is the type of Exception and the values are the count of the exception Id's so that we can see what types of exceptions become more and less frequent in each version.

 

I can create this chart but am having trouble sorting the Axis by increasing version number. I followed your instructions and now my dataset has a column containing the sortable version string, however it does not appear in the charts "Sort by" menu, which may have to do with the grouping/counting behavior.

Super User III
Super User III

Re: Sorting Version Number with multiple decimal points formatted as text

Hi @KyleB ,

 

Are you using that new column as you x-axis on your visual?

 

The Sort by on the visuals only takes into account information on the values, legends or axis.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Check out my blog: Power BI em Português


Highlighted
KyleB
Regular Visitor

Re: Sorting Version Number with multiple decimal points formatted as text

If I place my zero padded tool_version_sortable column on the Axis I can sort by it. However, we have many versions and I would like to display the un-padded values sorted by the padded ones for visual compactness. Adding both columns to the axis does not make this possible either.

 

My latest attempt at achieving this behavior is to create a new table from my runs table that has only version numbers and padded version numbers and then add a relation connecting it to the other table. I was hoping that if I placed version number on the axis the many-to-one relation would allow me to sort by the padded versions

Super User III
Super User III

Re: Sorting Version Number with multiple decimal points formatted as text

Hi @KyleB ,,

 

You need to sort the column you want to have in your visual by the one that as the padded version numbers.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Check out my blog: Power BI em Português


srelwani
New Member

Re: Sorting Version Number with multiple decimal points formatted as text

Hi @MFelix 

 

If I want to sort version numbers with upto 4 digits between the delimiters like the following numbers. Will the dax query work by just changing the padding number?

 

Text.PadStart ([Valid.1], 4, "0") & "." &
Text.PadStart ([Valid.2], 4, "0") & "." &
Text.PadStart ([Valid.3], 4, "0") & "." &
Text.PadStart ([Valid.4], 4, "0")

 

Example: 

WlanDriverVersion
20.70.4.2
19.51.15.3
15.16.0.2
2023.70.306.2018
20.70.6.1
19.51.8.3
3.0.2.201
15.68.9120.47
2024.0.3.101

 

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors