Sort number within a cell separated by comma Desktop

[hkbs]

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 example is like 3,2,1, 10,11,12in separated by comma

with the method on the old post below I can't sort it correctly as it will sort by like 1,10,11,12, 2,3so far and so on.

is there a way or hints how can I get around this?

(To be a result like 1,2,3,10,11,12)

I have tried 2 days , searched around but still have no joys 🥺 is there anychance someone can guide me out? it this possible to do under the power bi desktop?

Your help will be greatly appreciated.

Thanks indeed in advance

This is the old post

https://community.powerbi.com/t5/Desktop/Sort-number-within-a-cell-separated-by-comma/m-p/246504

One of the method I tried on there the 10 will become 1,0 instead of 10.

[hkbs]

@ImkeF 

Thanks indeed for all the help! This workssss!

You are absolutely star!!!!!!!!!
@amitchandak thanks so much for the intro too!

wish u both have a great day ! 🎉🥰😘sending lots of love to u both!

[amitchandak]

@ImkeF , can you help with this?

[ImkeF · Accepted solution]

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"