Forum Discussion
Help with "if column values do not match another column values then..."
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.
Hi EimearC
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
2 Replies
- PhilipTreacySuper User
Hi EimearC
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
- EimearCFrequent Visitor
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.