Forum Discussion
Anonymous
8 years agoNot applicable
Power BI - M Language - Strip Multiple Bracketed Text from single Text Field
Hi All, I have a field column which contains a name for example "Joe Bloggs (JBloggs)" And I have cobbled together the following to strip out the "(JBloggs)" Result = Table.AddColumn( Chg...
- 8 years ago
In the query below, second step inside out:
1. Names are split on "(" and ")"
2. Alternating rows are taken from the result, skipping 1 item, taking 1 item, starting with the first 1 item to take.
3. Resulting items are trimmed (leading/trailing spaces removed).
4. Result is combined with a space between each part.
let Source = #table(type table[Names = text], {{"Joe Blog (JBlogg) | Sam Smith (SSmith) | Andrew Cox (Acox)"}}), StrippedText = Table.AddColumn( Source, "StrippedNames", each Text.Combine( List.Transform( List.Alternate( Text.SplitAny( [Names], "()"), 1, 1, 1), Text.Trim), " "), type text) in StrippedText
MarcelBeug
Community Champion
8 years agoIn the query below, second step inside out:
1. Names are split on "(" and ")"
2. Alternating rows are taken from the result, skipping 1 item, taking 1 item, starting with the first 1 item to take.
3. Resulting items are trimmed (leading/trailing spaces removed).
4. Result is combined with a space between each part.
let
Source =
#table(type table[Names = text],
{{"Joe Blog (JBlogg) | Sam Smith (SSmith) | Andrew Cox (Acox)"}}),
StrippedText =
Table.AddColumn(
Source,
"StrippedNames",
each Text.Combine(
List.Transform(
List.Alternate(
Text.SplitAny(
[Names],
"()"),
1,
1,
1),
Text.Trim),
" "),
type text)
in
StrippedText
Anonymous
8 years agoNot applicable
Thanks MarcelBeug
Awesome use of M - and learnt loads from your code - many thanks - works a treat.