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

Question

I wonder if someone can help me.&nbsp;I have a table of student information in Power query with the following layout:&nbsp;Year GroupSubject Code10Ss11Ss12Ss13Ss11Pe12Ss13Ss10Pe&nbsp;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.&nbsp;The ideal outcome would be (based on the above data) - i.e. only the bold values have been changed.&nbsp;Year GroupSubject Code10Ss11Ss12Pe13Pe11Pe12Pe13Pe10Pe&nbsp;I would really appreciate it if anyone had any ideas and could help me out.Thanks so much,Ian

ifillingham · Accepted Answer

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!!

Forum Discussion

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

1 Reply

Related content

Replacing values with Iterations

Replace Errors values with Null values

Replace values

Date Different return wrong value

Replace multiple values at once (replace value)

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

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