Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
sherville
New Member

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.

2 ACCEPTED SOLUTIONS
Greg_Deckler
Super User
Super User

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"



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

v-shex-msft
Community Support
Community Support

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),","))

7.PNG

 

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 Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

6 REPLIES 6
ImkeF
Super User
Super User

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"

 

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

Anonymous
Not applicable

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

Hi Jorge,

please click on the link in my signature for the video-walkthrough:

 

ImkeF_0-1627581533696.png

 

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

FMC
Regular Visitor

 

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.

FMC_0-1635767334132.png

 

 

v-shex-msft
Community Support
Community Support

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),","))

7.PNG

 

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 Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Greg_Deckler
Super User
Super User

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"



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors