Forum Discussion
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.
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
12 Replies
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
Thank you, that worked! I appreciate your assistance.
Now I am working on displaying the original format in the sorted order in the visual. :smileyhappy: Always something to learn.
Hi,
I have a Rule_Name column which is a text field that I want to have it sorted based on 2.1...,2.2...,2.3....,18.1...,19.1....
For it first I tried to do text before delimiter to remove these number from the Rule_Name column into a new column. However, this "Text Before Delimiter" column is also a text column and I am not able to sort in correct. Could you please assist me with this?Thanks,
Umar
Hi umarfarooq4
Try to add the following custom colum:
Text.PadStart(Text.BeforeDelimiter([Rule_Name], ".", 0), 3, "0") & "." & Text.PadStart( Text.BeforeDelimiter(Text.AfterDelimiter([Rule_Name], ".", 0), ".", 0), 3, "0" ) & "." & Text.PadStart( Text.BeforeDelimiter( Text.BeforeDelimiter(Text.AfterDelimiter([Rule_Name], ".", 1), ".", 1), ".", 0 ), 3, "0" ) & "." & Text.PadStart(Text.AfterDelimiter( Text.BeforeDelimiter( [Rule_Name] , " ") , ".", 2), 3, "0")Result below:
