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?
- MarcelBeug
Community Champion
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 StrippedTextThanks MarcelBeug
Awesome use of M - and learnt loads from your code - many thanks - works a treat.
Thanks Ross - yes I did try that route but I couldn't keep it dynamic - varying datasets would contain different limits - sometimes 2 sometimes 3 or indeed more - which answers your question about hard limits - the good news is that MarcelBeug seems to have nailed it - bu thanks for responding.