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

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
Kagliostro
Frequent Visitor

Sort text in a single cell

Hi everyone,
I am would like to sort out text in a single cell and to do so I found in this forum a script in M which would fit perfectly with my need (link). However, while the majority of cells would have comma separated values, there are some with a single value. Cells with single values generates an error that I could circumvent with an if, but I am struggling to achieve it

 

 

let
    Split = Text.Split([NAME_ASSIGNED], ","),
    Trim = List.Transform(Split, each Text.Trim(_)),
    Sort = List.Sort(Trim),
    Combine = Text.Combine(Sort,",")
in
if List.Count([NAME_ASSIGNED],",") > 1 then Combine
    else [NAME_ASSIGNED]

 

 

 

1 ACCEPTED SOLUTION
Ahmedx
Super User
Super User

There shouldn't be an error, but try this code anyway

=[
  f = (x) => Text.Combine(List.Sort(List.Transform(Text.Split(x, ","), Text.Trim)), ","),
  Split = try f([NAME_ASSIGNED]) otherwise [NAME_ASSIGNED]
][Split]

 

Screenshot_1.png

View solution in original post

4 REPLIES 4
ronrsnfld
Super User
Super User

If your NAME_ASSIGNED column contains various text strings, I'm surprised your code works at all. Your last line reads:

if List.Count([NAME_ASSIGNED],",") > 1 then Combine
    else [NAME_ASSIGNED]

 

which should always result in an error if NAME_ASSIGNED contains text strings.

 

What is actually in that column, and what is your error message.

Ahmedx
Super User
Super User

There shouldn't be an error, but try this code anyway

=[
  f = (x) => Text.Combine(List.Sort(List.Transform(Text.Split(x, ","), Text.Trim)), ","),
  Split = try f([NAME_ASSIGNED]) otherwise [NAME_ASSIGNED]
][Split]

 

Screenshot_1.png

Omid_Motamedise
Super User
Super User

Rewrite the first step by the below formula

 

Split = if Text.Contains([NAME_ASSIGNED],",") then Text.Split([NAME_ASSIGNED], ",") else {[NAME_ASSIGNED]},
If my answer helped solve your issue, please consider marking it as the accepted solution. It helps others in the community find answers faster—and keeps the community growing stronger!
You can also check out my YouTube channel for tutorials, tips, and real-world solutions in Power Query with the following link
https://youtube.com/@omidbi?si=96Bo-ZsSwOx0Z36h
lbendlin
Super User
Super User

There should not be an error, and there is no need to treat a single item list differently.

 

 

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText("i45W8s9L1VEIKc/XUVBQcMssS1WK1YlWCskoSgWyYgE=", BinaryEncoding.Base64), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [NAME_ASSIGNED = _t]
  ), 
  #"Added Custom" = Table.AddColumn(
    Source, 
    "Custom", 
    each 
      let
        Split   = Text.Split([NAME_ASSIGNED], ","), 
        Trim    = List.Transform(Split, each Text.Trim(_)), 
        Sort    = List.Sort(Trim), 
        Combine = Text.Combine(Sort, ",")
      in
        Combine
  )
in
  #"Added Custom"

 

 

What's the error message?

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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

Top Solution Authors