Help with "if column values do not match another column values then..."

[EimearC]

Hi there,

I'm hoping someone can please assist me with this M code I am trying to impliment: 

I have a small table called TableCR which has 5 values:

1-EXCELLENT

2-GOOD

3-FAIR

4-POOR

5-UNSERVICEABLE

Then I have another main table called Audit. One of the columns within the Audit table is called Rating. What I am hoping for is that I can do a lookup from the Rating column to state that if any of the values within the Rating column does not equal any of the 5 values within the TableCR table, column called "RatingCheck" then I apply a default value,"3-FAIR", to the non matching value. 

If the values are within the other table, then leave as is. 

This is what I have currently but it's replacing every single value with "3-FAIR" instead of just the non matching values, can anyone please assist me to tweak this to get it to work as hoped? 

= Table.ReplaceValue(#"Replaced CRating", each [Rating], each if [Rating] <> TableCR[RatingCheck] then "3-FAIR" else [Rating], Replacer.ReplaceValue, {"Rating"} )

 Thank you so much for your time.

[EimearC]

Thanks so much @PhilipTreacy for the workaround. I will use that solution.

If anyone else does read this & might be able to solve why my original M Code is currently not working though I would love to understand why that is. 

[PhilipTreacy · Accepted solution]

Hi @EimearC 

Download example PBIX file

Might be simpler to add a Custom Column

if not List.ContainsAny(TableCR[RatingCheck], {[Rating]}) then "3-FAIR" else [Rating]

then delete the old rating column and rename the new column you just created.

Phil