Power BI - M Language - Strip Multiple Bracketed Text from single Text Field

[Anonymous]

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

[Anonymous]

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?

[Anonymous]

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.

[MarcelBeug · Accepted solution]

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

[Anonymous]

Thanks MarcelBeug

Awesome use of M - and learnt loads from your code - many thanks - works a treat.