Forum Discussion
Sort number within a cell separated by comma
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
Community ChampionJust 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"
Hi Imke,
Sorry for the question but I am new at this.
I need to apply this script but I don't know how. Could you pleaselet me know or refer to other post where I could learn how to run this in power bi?
Thanks in advance,
Jorge
- ImkeF
Hi Jorge,
please click on the link in my signature for the video-walkthrough:
How to convert a column text from "zzz abc | aaa abc" to custom column "aaa abc | zzz abc" ?
I'm using the following code
let Source = Table.FromRows([ResultDetails], 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"But I'm getting following error.
