sorting numbered bullet points

[GabrielN81]

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!

[Anonymous · Accepted solution]

Hi @GabrielN81 ,

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 Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Idrissshatila]

Hello @GabrielN81 ,

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 👍

Follow me on Linkedin