Replace values in a column based on values in a different column

[ifillingham]

I wonder if someone can help me.

I have a table of student information in Power query with the following layout:

Year Group	Subject Code
10	Ss
11	Ss
12	Ss
13	Ss
11	Pe
12	Ss
13	Ss
10	Pe

I would like to change the Ss values in the "Student Group" column to Pe BUT only if the Year group is 12 or 13. I have spent quite a while trying to find a solution (ideally a single step) but I haven't been successful.

The ideal outcome would be (based on the above data) - i.e. only the bold values have been changed.

Year Group	Subject Code
10	Ss
11	Ss
12	Pe
13	Pe
11	Pe
12	Pe
13	Pe
10	Pe

I would really appreciate it if anyone had any ideas and could help me out.

Thanks so much,

Ian

[ifillingham · Accepted solution]

I've found the solution so wanted to close this before anyone commented!!

I created a custom column:

Table.AddColumn(#"Capitalized Subject code", "Change KS5 Ss to Pe", each if [Subject Code] = "Ss" and [YearCode] >= 12 then "Pe" else [Subject Code])

"Capitalized Subject code" and  "Change KS5 Ss to Pe" refer to parts of the puery to make it easier for mne to follow what I did so I can do it again if I need to!!