Forum Discussion
sorting numbered bullet points
Hi everyone, hoping someone can help on this.
I have a list of bullet points in a table visual such as the following: 1; 1.1; 1.2; 1.3 all the way through 1.13,
In ordering this list in the table, I get 1.10 before 1.2
How do I get it reordered so that 1.2 comes before 1.10?
See screen capture:
Thanks!
Hi Anonymous ,
I suggest you to create a [ID Order] column to sort your [ID] column.
Please refer to my M code.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQzVIrVAdFGUNoYSptAaVMobWgAY8B0GMK0GEL0GEHNMoKaZaQHFzeAMWAqQHpjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"ID", Order.Ascending}}), #"Duplicated Column" = Table.DuplicateColumn(#"Sorted Rows", "ID", "ID - Copy"), #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "ID - Copy", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"ID - Copy.1", "ID - Copy.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"ID - Copy.1", Int64.Type}, {"ID - Copy.2", Int64.Type}}), #"Duplicated Column1" = Table.DuplicateColumn(#"Changed Type1", "ID - Copy.2", "ID - Copy.2 - Copy"), #"Changed Type2" = Table.TransformColumnTypes(#"Duplicated Column1",{{"ID - Copy.2 - Copy", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type2", "ID Order", each [#"ID - Copy.1"]*Number.Power(10,Text.Length([#"ID - Copy.2 - Copy"])) + [#"ID - Copy.2"]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"ID - Copy.1", "ID - Copy.2", "ID - Copy.2 - Copy"}), #"Changed Type3" = Table.TransformColumnTypes(#"Removed Columns",{{"ID Order", Int64.Type}}) in #"Changed Type3"New Table:
Select [ID] column and sort it by [ID Order] in Column Tool. Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hello Anonymous ,
the 1.2 is being read as 1.20 thus its showing after 1.2.
so in order to show it before, then it should be 1.02 so it would show as u want.
but as sorting, it is working fine because its reading it as 1.20.
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍
Hi Anonymous ,
I suggest you to create a [ID Order] column to sort your [ID] column.
Please refer to my M code.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQzVIrVAdFGUNoYSptAaVMobWgAY8B0GMK0GEL0GEHNMoKaZaQHFzeAMWAqQHpjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"ID", Order.Ascending}}), #"Duplicated Column" = Table.DuplicateColumn(#"Sorted Rows", "ID", "ID - Copy"), #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "ID - Copy", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"ID - Copy.1", "ID - Copy.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"ID - Copy.1", Int64.Type}, {"ID - Copy.2", Int64.Type}}), #"Duplicated Column1" = Table.DuplicateColumn(#"Changed Type1", "ID - Copy.2", "ID - Copy.2 - Copy"), #"Changed Type2" = Table.TransformColumnTypes(#"Duplicated Column1",{{"ID - Copy.2 - Copy", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type2", "ID Order", each [#"ID - Copy.1"]*Number.Power(10,Text.Length([#"ID - Copy.2 - Copy"])) + [#"ID - Copy.2"]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"ID - Copy.1", "ID - Copy.2", "ID - Copy.2 - Copy"}), #"Changed Type3" = Table.TransformColumnTypes(#"Removed Columns",{{"ID Order", Int64.Type}}) in #"Changed Type3"New Table:
Select [ID] column and sort it by [ID Order] in Column Tool. Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
