Splitting degrees into multiple categories: Power Query or DAX?

[montyfern]

Season's Greetings!

If I have a table of data listing all their degrees together ie.., Smith, Joan, AB, MS, MD, PhD., etc., pretending Joan earned all those degrees, I'd please like to add one column added to the table that lists the doctoral degree category for each person. Two of the categories need to include multiple degrees (i.e., MD + PHD, PHD + Other Professional degree.

Thanks very much!

 

[alansidman]

-How about posting some sample data 8-10 records using XL2BB. Then post the results you would like to see for those 8-10 records. If you are unfamiliar with XL2BB, then look at my signature. Do not post a picture as we can not manipulate data in a picture.

 

[montyfern]

Hello alansidman & happy holidays! Unfortunately I can't download your recommended app due to security protocols. Apologies! But here's the data, it's quite simple but I'm at a loss. Tried splitting in power query, extracted & parsed, but she needs each category of advanced degree, so five new columns, & all relevant degree data landing there. Short of an xlookup, I'm not getting it. Here's the data:

Degrees

BS

BS

AB

BA

MPH

BA

MD

BS

BA

BS

BS

MD

BA

BA

BA

BS

BS

BS

BA

BA

BS

BS

MD

BA

MA

BA

BS

AB

BS

BA

BS

BA

BS

DVM

PHMD

BS

BS

 

[alansidman]

I'm not understanding what you want to do with the data you provided. Show us a mocked up solution for the data provided.

 

[montyfern]

I'm not understanding what you want to do with the data you provided. Show us a mocked up solution for the data provided.

Can you conceptualize it? It's easy. Just imagine a bunch of names:
Joan Smith MD, Ph.D, DO
John Smith, MD, MS, Ph.D.
Tom Smith, MS
Adelle Smith, MBA
Ezekial Jones, MD, Ph.D
Emerett Meyer-Schwab, Ph.D.
Mary-Ellen Chaddock, MA, DO
Chad Bradford, MBA
etc. etc.

They want all their advanced degrees to show up in rows, organized, like this:
. I need one column added to the table that lists the a doctoral degree category for each person. Two of the categories need to include multiple degrees (i.e., MD + PHD, PHD + Other Professional degree.

I see some ways I can do it, but it would be slower than manually assigning categories. I tried splitting, grouping, adding columns, merging, pivoting, unpivoting, conditional columns, IF statements, even using replacement M code. Stumped. Don't think it's possible.

Grateful for your help alan!
-montyfern

 

[alansidman]

I am not a mind reader and my crystal ball was foreclosed on for lack of payments. Show me some sample data to work with. I am not going to create your files for your issues when you already have them. If you are unwilling to show a before and after using XL2BB, then I have no time to spend on this exercise. Up to you to help us to help you.

 

[montyfern]

Hmmm...when I first read this have to admit, was put off. I've been in your shoes a bunch of times as a help desk technician but never used your tone. So, in any event, here's some sample data.

 

[alansidman]

Unpivot your data with Power Query and then load the data into Power Pivot and build a measure to group the degrees in one field using the Concatenatex function. Would like to have seen your data loaded using XL2BB instead of a picture that cannot be manipulated. Would have been able to provide a viable demo with that capability.

 

[montyfern]

Hi alansidman, unfortunately I can't install XL2BB. I know the picture is tiny, it's hard for you to figure out exactly what's going on. I know power pivot, DAX, & how to build a measures, so will try that route. Thanks for the suggestions & appreciate the kind reply!