Forum Discussion
Power BI - M Language - Strip Multiple Bracketed Text from single Text Field
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
Do you have any hard limits on how many names might be contained in a single entry? Could you use the 'split columns by delimiter' function in order to get your names into multiple columns? Then apply your removal method to each column, then finally either pivot or concatinate the names again?
Community ChampionIn 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
Thanks MarcelBeug
Awesome use of M - and learnt loads from your code - many thanks - works a treat.