Forum Discussion
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( ChgTyp, "Names", each Text.ReplaceRange( [Names], Text.PositionOf( [Names], "("), Text.PositionOf( [Names], ")") - Text.PositionOf( [Names], "(") + 1, ""))My problem is now extending this to work for circumstances where there are 2, 3 or more names in the same field with a similar format ideally from the same single Table.AddColumn statement.
eg. Joe Blog (JBlogg) | Sam Smith (SSmith) | Andrew Cox (Acox)
I did take a look at the List.Generate function but got bogged down in the syntax... and couldn't get it to work... the approach I was taking was exiting based on List.PositionOf but like I said I was struggling.
Any assistance gratefully received - and thanks in anticipation
Jabba
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
4 Replies
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