change cell contents based on word in the cell

[jblackburnHWGA]

I'm working on some class codes and i want to make a column to show the subject based on the class code as i can't extract a subject column from the source.

Class code 7h-9x-ART - 22/23 tells me the subject is Art.

Can i duplicate the column then make power bi replace all cells with the Art in them with the word Art for the whole cell

At the moment it just changes the word Art in the class code but keeps the other part of the cell. 

The class codes differ the only constant is the subject code and the year at the end

[ADPowerBI1]

is there any consistency in the format? Like is the subject always after a dash ( - )?

[jblackburnHWGA]

The class names differ annoyingly. too much.

for example

10A-Art - ART - 22/23

9S-Ar - ART - 22/23

11C-CD1 - CHD - 22/23

10C-Bus2 - OBC - 22/23

I think i've thought of another way, the output only uses the e-mail address so i was going to add in a staff list. I can add the subject to the staff list as it wouldn't be changable where the other report would change daily. I can then link the e-mail and use filter by subject

[ADPowerBI1]

If i understand correctly follow these steps:

1. go to the power query editor

2. click on the column that contains the codes.

3. right click that column and select "replace values"

4. Do this for each of the codes, replacing them with the subject. 

Hope this helps! If i'm getting confused by the question please reword it for me and i'll do my best! 🙂 if this helps please accept as solution.

[ADPowerBI1]

P.s. If you want to keep the class code column you can duplicate it then make all your "replace values" steps.

[jblackburnHWGA]

I'm talking about replacing the cell contents based on something in the cell or another cell.

There are 240 class codes but only maybe 20 subjects. So as an example for Art

i'll have for year 7 - 7h-art, 7g-art, 7j-art, 7t-art

I know these classes are Art. How do i get bi to push the word Art into a column so i can filter as art.

The formating isn't always the same as well science has the set reference in and SCI as the reference to science.

The replace values just changes the reference in the cell.