Forum Discussion

EimearC's avatar
EimearC
Frequent Visitor
2 years ago
Solved

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 

     

    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

2 Replies

  • 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

  • EimearC's avatar
    EimearC
    Frequent 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.