Forum Discussion
Sort number within a cell separated by comma Desktop
Sort number within a cell separated by comma
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.
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"
3 Replies
ImkeF , can you help with this?
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"
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!
