Forum Discussion
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
Super UserHi 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.
- MFelix
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.
Regards,
MFelix
Thank you again! Problem solved.
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.
