Extract value matching criteria from comma separated string/values

[nomanches]

Hello, 

I hope I can get some help on this, my apologies if this is listed incorrectly as its my first post. 

I recently started using PowerBi and what a tool it is, I have been able to look up certain values in a column when its a single entry per cell/row. The issue that I am having now is I want to extract the text between the semicolons but the problem is that the content found between the semicolons does not align per type of advisor/coach and in some cases the cell can be empty. 

Here is an example of the column in question, I would like to extract only those marked as (advisor) to a new column called Advising Assigned Advisors.

Assigned Advisors

Gills, Sean (Advisor); Martinez, Checo (Res Life Success Coach); Reyna, Patricia (IDEA Success Coach); Ayende, Carolina (IDEA Success Coach); Vigaet, Casey (IDEA Success Coach); Wanda, Jennifer (IDEA Success Coach); Swiss, Roxanne (IDEA Success Coach)

Irena, Ilse (Advisor); Villa, Abraham (Success Coach); Reyna, Patricia (IDEA Success Coach); Ayende, Carolina (IDEA Success Coach); Vigaet, Casey (IDEA Success Coach); Wanda, Jennifer (IDEA Success Coach); Swiss, Roxanne (IDEA Success Coach)

Yan, Victor (Advisor); Garza, Erika (Success Coach); Reyna, Patricia (IDEA Success Coach); Ayende, Carolina (IDEA Success Coach); Vigaet, Casey (IDEA Success Coach); Wanda, Jennifer (IDEA Success Coach); Swiss, Roxanne (IDEA Success Coach)

Reyna, Patricia (IDEA Success Coach); Ayende, Carolina (IDEA Success Coach); Vigaet, Casey (IDEA Success Coach); Wanda, Jennifer (IDEA Success Coach); Swiss, Roxanne (IDEA Success Coach); Gozado Zepeda, Maria (Advisor)

Reyna, Patricia (IDEA Success Coach); Rosadito, Michael (Advisor); Ayende, Carolina (IDEA Success Coach); Vigaet, Casey (IDEA Success Coach); Wanda, Jennifer (IDEA Success Coach); Swiss, Roxanne (IDEA Success Coach)

Reyna, Patricia (IDEA Success Coach); Ayende, Carolina (IDEA Success Coach); Vigaet, Casey (IDEA Success Coach); Wanda, Jennifer (IDEA Success Coach); Swiss, Roxanne (IDEA Success Coach); Hernan, Giovanni (Advisor)

Hernan, Giovanni (Advisor); Vigaet, Casey (IDEA Success Coach); Wanda, Jennifer (IDEA Success Coach); Swiss, Roxanne (IDEA Success Coach)

Reyna, Patricia (IDEA Success Coach); Ayende, Carolina (IDEA Success Coach); Vigaet, Casey (IDEA Success Coach); Wanda, Jennifer (IDEA Success Coach); Capilla, Maritere (Advisor); Swiss, Roxanne (IDEA Success Coach)

Reyna, Patricia (IDEA Success Coach); Ayende, Carolina (IDEA Success Coach)

I would like to be able to add a new column that will extract those values that are marked as (Advisor) only and place them in a new column. I already tried using the extract and parse options but they do not extract the data correctly. I also split the columns and tried to find the *(Advisor) in each column and pass it over to the new one but could not get that to work. 

Surely there is a way to do I am just not able to locate the solution and have siffted through a lot of blogs and videos but had no luck. Any help here would be great, thanks in advance. 

[rsbin]

@nomanches ,

Please see attached file with Power Query steps.

The first step is to Split by Delimiter - semi-colon.  But be sure to use the Advanced Options and split into rows.

Then, hope you can follow the rest of the logic.

Hope this helps.

Regards,

[nomanches]

Thank you for the prompt turn around, I should have added that the data is setup like seen below, how can I apply it to this scenario. 

ID	Assigned Advisors	Advising Assigned Advisor (Desired OUTPUT)
1230123	Gills, Sean (Advisor); Martinez, Checo (Res Life Success Coach); Reyna, Patricia (IDEA Success Coach); Ayende, Carolina (IDEA Success Coach); Vigaet, Casey (IDEA Success Coach); Wanda, Jennifer (IDEA Success Coach); Swiss, Roxanne (IDEA Success Coach)	Gills, Sean (Advisor)
2239321	Irena, Ilse (Advisor); Villa, Abraham (Success Coach); Reyna, Patricia (IDEA Success Coach); Ayende, Carolina (IDEA Success Coach); Vigaet, Casey (IDEA Success Coach); Wanda, Jennifer (IDEA Success Coach); Swiss, Roxanne (IDEA Success Coach)	Irena, Ilse (Advisor)
3248519	Yan, Victor (Advisor); Garza, Erika (Success Coach); Reyna, Patricia (IDEA Success Coach); Ayende, Carolina (IDEA Success Coach); Vigaet, Casey (IDEA Success Coach); Wanda, Jennifer (IDEA Success Coach); Swiss, Roxanne (IDEA Success Coach)	Yan, Victor (Advisor)
4257717	Reyna, Patricia (IDEA Success Coach); Ayende, Carolina (IDEA Success Coach); Vigaet, Casey (IDEA Success Coach); Wanda, Jennifer (IDEA Success Coach); Swiss, Roxanne (IDEA Success Coach); Gozado Zepeda, Maria (Advisor)	Gozado Zepeda, Maria (Advisor)
5266915	Reyna, Patricia (IDEA Success Coach); Rosadito, Michael (Advisor); Ayende, Carolina (IDEA Success Coach); Vigaet, Casey (IDEA Success Coach); Wanda, Jennifer (IDEA Success Coach); Swiss, Roxanne (IDEA Success Coach)	Rosadito, Michael (Advisor)
6276113	Reyna, Patricia (IDEA Success Coach); Ayende, Carolina (IDEA Success Coach); Vigaet, Casey (IDEA Success Coach); Wanda, Jennifer (IDEA Success Coach); Swiss, Roxanne (IDEA Success Coach); Hernan, Giovanni (Advisor)	Hernan, Giovanni (Advisor)
7285311	Hernan, Giovanni (Advisor); Vigaet, Casey (IDEA Success Coach); Wanda, Jennifer (IDEA Success Coach); Swiss, Roxanne (IDEA Success Coach)	Hernan, Giovanni (Advisor)
8294509	Reyna, Patricia (IDEA Success Coach); Ayende, Carolina (IDEA Success Coach); Vigaet, Casey (IDEA Success Coach); Wanda, Jennifer (IDEA Success Coach); Capilla, Maritere (Advisor); Swiss, Roxanne (IDEA Success Coach)	Capilla, Maritere (Advisor)
9303707	Reyna, Patricia (IDEA Success Coach); Ayende, Carolina (IDEA Success Coach)

[rsbin]

@nomanches ,

Please review Advisors2.

Let me know if this works for you.

Regards,

[rsbin]

@nomanches ,

Here is a 3rd Version for you to review.

Realized that I filtered out records that did not contain (Advisor).

I believe this version works if you need to retain all records including where Advisor = blank.

Regards,

[nomanches]

@rsbin 

Thanks for catching that, I did need to identify those that are not assigned and this helps do that. Although I was able to follow the steps you provided, I am getting an error and it appears to happen at the Find Advisor (Custom Column), this is what it looks like in the data set.

I noticed the red/error notification on the columns appeared after this step and when I click on the error link it displays the following:

When I go to close and apply the changes I am getting the following error message:

Thanks once again for taking the time look at my issue. 

[rsbin · Accepted solution]

Good Morning @nomanches ,

Appears you have blank records in your raw data set.

Best to remove those prior to import so you have a "clean" dataset.  If you are unable, then I added a first step in the Power Query to filter out those records where Assigned Advisors is null or blank.

Please see attached.

Best Regards,

[nomanches]

Good Morning @rsbin , 

Thank you for taking the time to assit me, I followed your recommendations and was able to get the list of advisors extracted and retained the blank cells as well. Thanks to you I have a file I can now work with. 

Have a great day!