Sorting Version Number with multiple decimal points formatted as text

[pchilton]

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.

[MFelix · Accepted solution]

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