Forum Discussion
Extract value matching criteria from comma separated string/values
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,
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,
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) |
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,
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.