Replace text using wildcard

[mikeymay]

I have a column of text that contains some text and example of which is below
Bloggs Joe, GB Sales:Required;Smith John, GB Sales:Required;Jones Sally, GB ASC:Required;Walker, Alan, GB Sales:Required

There could be a number of combinations of the 'GB ?:Required' text and I won;t know what these text strings will be

Basically what I need to do is just have the column contain the names so the above would be as follows
Bloggs Joe, Smith John, Jones Sally, Walker, Alan

Sometimes there will only be one name as follows
Bloggs Joe, GB Sales:Required

For this I would only need
Bloggs, Joe
to be in the column

 

[VBA Geek]

Does this do what you want?

let
    Source = "Bloggs Joe, GB Sales:Required;Smith John, GB Sales:Required;Jones Sally, GB ASC:Required;Walker, Alan, GB Sales:Required",


    fnGetNames = (S as text) as text =>
        let
            MidTxt = Text.BetweenDelimiters( S, "GB ", ":Required;",0,0),
            ReplcText = if 
                            MidTxt = "" 
                        then 
                            S 
                        else
                            @fnGetNames( Text.Replace(S, "GB " & MidTxt & ":Required;","") )
        in
            ReplcText,
                        
    Test = fnGetNames( Source & ";" )
in
    Test

 

[mikeymay]

Thanks, I'll give that a go and let you know.

 

[mikeymay]

Plugged that in and it works a treat! Thanks!

The only thing I need to do then is to remove the last column. Is it as simple as replacing the last character with ""?

 

[mikeymay]

When I try to look at this in Power Pivot the query is failing to load and I'm getting a stack overflow error.

Will this eat up a lot of memory?

 

[VBA Geek]

How many rows do you have? How are you calling the function?

 

[mikeymay]

46k rows.

The message I get is -
Excel couldn't refresh the table 'CRM' from connection 'Query - CRM'. Here;s the error messages from the external data source:

The operation failed because the database does not exist, the source table doesn't not exist or because you do not have access to the data source.

More Details:
OLE DB or ODBC error:Evaluation resulted in a stack overflow and cannot continue.
An error occurred while processing table 'CRM'.
The current operation was cancelled because another operation in the transaction failed.

 

[VBA Geek]

Hello mikeymay, I have tested it on 46k rows and it seems fine on my side. How are you calling the function? Can you show us the M code?

 

[mikeymay]

I'm new to PQ so I may not have done this properly...

In the Power Query Editor window, I clicked on the Add Column tab, then Custom Column and then added the code you supplied changing the static, names/text to the column it needs to read so it looks like this in the PQE window

= Table.AddColumn(#"Added Custom1", "New Part", each let
    Source = [Participant],


    fnGetNames = (S as text) as text =>
        let
            MidTxt = Text.BetweenDelimiters( S, "GB ", ":Required;",0,0),
            ReplcText = if 
                            MidTxt = "" 
                        then 
                            S 
                        else
                            @fnGetNames( Text.Replace(S, "GB " & MidTxt & ":Required;","") )
        in
            ReplcText,
                        
    Test = fnGetNames( Source & ";" )
in
    Test)

I close & Load and I can see the new column when I go to manage the model but in Excel when it tries to download, I get the error.

 

[VBA Geek]

you need to do something like this (Your M code has to end like this, after Added Custom 1

fnGetNames = (S as text) as text =>
        let
            MidTxt = Text.BetweenDelimiters( S, "GB ", ":Required;",0,0),
            ReplcText = if 
                            MidTxt = "" 
                        then 
                            S 
                        else
                            @fnGetNames( Text.Replace(S, "GB " & MidTxt & ":Required;","") )
        in
            ReplcText,

    NewTable = Table.AddColumn(#"Added Custom1", "New Part", each fnGetNames ( [Participant] & ";" ), type text)
in
    NewTable