Forum Discussion
Sort number as text from a list
Hi,
I'm trying to sort number separated by comma in descending order in a list.
I have a column of 3 number in text format, each separated by a comma
I would like to sort each numbers in each row by descending order.
I tried to transform the text and split them into number before sorting them, but i cannot find how to do that.
I also tried to transform them into a list, but i cannot find how to order the list.
How can i do that ??
(sorry for my english)
Or maybe you want this...
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtWx0DFSitUBsYx1LMEsQx1TqJghUMwQzLLQMdAxhDENjXQMDZRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Added Custom" = Table.AddColumn(Source, "ListOfNumbers", each List.Sort( List.Transform(Text.Split([Column1],","), Number.From) , Order.Descending)), #"Added Custom1" = Table.AddColumn(#"Added Custom", "BackToText", each Text.Combine(List.Transform( [ListOfNumbers], Text.From) ,",")) // This Step can be merged in single step with the previous one. in #"Added Custom1"
5 Replies
Or maybe you want this...
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtWx0DFSitUBsYx1LMEsQx1TqJghUMwQzLLQMdAxhDENjXQMDZRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Added Custom" = Table.AddColumn(Source, "ListOfNumbers", each List.Sort( List.Transform(Text.Split([Column1],","), Number.From) , Order.Descending)), #"Added Custom1" = Table.AddColumn(#"Added Custom", "BackToText", each Text.Combine(List.Transform( [ListOfNumbers], Text.From) ,",")) // This Step can be merged in single step with the previous one. in #"Added Custom1"That works !
Thank you so much 🙂
See if this helps.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WstAxNNIxNFSK1YlWMtEx07EEs4CixjrGSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3"}), #"Sorted Rows" = Table.Sort(#"Split Column by Delimiter",{{"Column1.1", Order.Ascending}}) in #"Sorted Rows"THis turn this:
into this:
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.That's not exactly the solution i was looking for, but thank you for taking the time to answer my question 🙂
