Sort number within a cell separated by comma

[sherville]

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.

[Greg_Deckler · Accepted solution]

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"

[ImkeF]

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"

[Anonymous]

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:

[FMC]

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.

[v-shex-msft · Accepted solution]

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

[Greg_Deckler · Accepted solution]

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"