Forum Discussion

hkbs's avatar
hkbs
Frequent Visitor
5 years ago
Solved

Sort number within a cell separated by comma Desktop

Sort number within a cell separated by comma Desktop   Hi I am hoping that if anyone around can guide me a way on this, I have similar issue of a old post (link below) however  involved to cell ex...
Need Help
Tips and Tricks
  • ImkeF's avatar
    ImkeF
    5 years ago

    Sure,

    you have to transform to number in order to get the sort right and then back to text in order to combine them in once field again:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTQMTTVMdIx0zHXsdAxUIrVAYpZ6hiZ6BgaAwXAUkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Sort DESC", each Text.Combine(List.Transform(List.Sort(List.Transform(Text.Split([Value],","), Number.From),Order.Descending), Text.From),","))
in
    #"Added Custom"

     

ImkeF's avatar
ImkeF
Icon for Community Champion rankCommunity Champion
5 years ago

Sure,

you have to transform to number in order to get the sort right and then back to text in order to combine them in once field again:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTQMTTVMdIx0zHXsdAxUIrVAYpZ6hiZ6BgaAwXAUkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Sort DESC", each Text.Combine(List.Transform(List.Sort(List.Transform(Text.Split([Value],","), Number.From),Order.Descending), Text.From),","))
in
    #"Added Custom"