Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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]
Solved! Go to Solution.
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]
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.
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]
Rewrite the first step by the below formula
Split = if Text.Contains([NAME_ASSIGNED],",") then Text.Split([NAME_ASSIGNED], ",") else {[NAME_ASSIGNED]},
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?