Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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!
@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"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
105 | |
79 | |
69 | |
62 |
User | Count |
---|---|
142 | |
106 | |
103 | |
85 | |
70 |