Forum Discussion
Sort number within a cell separated by comma
Hi, guys! I just wanna know if there is any way to sort a value within a cell that's separated by comma
Like for example, my column has a value of 4,5,2,3,7,8,6,1
is there any way I could sort this and make it as 1,2,3,4,5,6,7,8 or the other way around like 8,7,6,5,4,3,2,1?
Thank you! I hope anyone could help me with this.
You could do it in Query Editor:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtEx1THSMdYx17HQMdMxVIqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Added Custom" = Table.AddColumn(Source, "Custom", each List.Sort(List.RemoveItems(Text.ToList([Column1]),{","}))), #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}) in #"Extracted Values"To reverse sort, use the optional second parameter for your List.Sort " , Order.Descending"
Hi sherville,
For your scenario, I think you can add a custom column with 'Text.Split', 'List.Sort', 'Text.Combine' to achieve your requirement.
Sample:
#"Added Custom" = Table.AddColumn(#"Changed Type", "Sort DESC", each Text.Combine(List.Sort(Text.Split([Value],","),Order.Descending),",")) #"Added Custom1" = Table.AddColumn(#"Added Custom", "Sort ASC", each Text.Combine(List.Sort(Text.Split([Value],","),Order.Ascending),","))Full Query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQx1THSMdMx17HQMVCK1YlWstQx0TEG8sDiSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type 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.Sort(Text.Split([Value],","),Order.Descending),",")), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Sort ASC", each Text.Combine(List.Sort(Text.Split([Value],","),Order.Ascending),",")) in #"Added Custom1"Function Description List.Sort Returns a sorted list using comparison criterion. Text.Combine Returns a text value that is the result of joining all text values with each value separated by a separator. Text.Split Returns a list containing parts of a text value that are delimited by a separator text value. Regards,
Xiaoxin Sheng
6 Replies
Hi sherville,
For your scenario, I think you can add a custom column with 'Text.Split', 'List.Sort', 'Text.Combine' to achieve your requirement.
Sample:
#"Added Custom" = Table.AddColumn(#"Changed Type", "Sort DESC", each Text.Combine(List.Sort(Text.Split([Value],","),Order.Descending),",")) #"Added Custom1" = Table.AddColumn(#"Added Custom", "Sort ASC", each Text.Combine(List.Sort(Text.Split([Value],","),Order.Ascending),","))Full Query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQx1THSMdMx17HQMVCK1YlWstQx0TEG8sDiSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type 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.Sort(Text.Split([Value],","),Order.Descending),",")), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Sort ASC", each Text.Combine(List.Sort(Text.Split([Value],","),Order.Ascending),",")) in #"Added Custom1"Function Description List.Sort Returns a sorted list using comparison criterion. Text.Combine Returns a text value that is the result of joining all text values with each value separated by a separator. Text.Split Returns a list containing parts of a text value that are delimited by a separator text value. Regards,
Xiaoxin Sheng
- ImkeF
Community Champion
Just in case you want to sort numbers with more than 1 digit, transformation to number (for the sort) and then back to text (to combine back into one field) is needed:
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
Hi Jorge,
please click on the link in my signature for the video-walkthrough:
- Greg_Deckler
You could do it in Query Editor:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtEx1THSMdYx17HQMdMxVIqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Added Custom" = Table.AddColumn(Source, "Custom", each List.Sort(List.RemoveItems(Text.ToList([Column1]),{","}))), #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}) in #"Extracted Values"To reverse sort, use the optional second parameter for your List.Sort " , Order.Descending"
