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

Power Query
1

Hi there,

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

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”} )

I have created a helper column in the meantime to get it working as I need but I’m just wondering why the code above isn’t working for me if anyone might be able to enlighten me a bit?
Thank you so much for your time.

2

Hi @Eimear ,

Welcome to Forum.
I ask EDNA AI for help - here is the answer :

https://mentor.enterprisedna.co/account/documents/160

Hope it helps.
If not could you provide sample pbix file as well?

Thanks.

3

@mspanic
Thank you so much for the reply & for going to ENDA AI, I didn’t think of that at all.
The response makes a lot of sense & I look forward to giving the suggestion a go.

Thank you again :slight_smile:
Eimear

1 Like
4

Hi @Eimear,

The comparison with TableCR[RatingCheck] in your code will always evaluate to false as you are comparing a text type with a list type.

Give this a go:

let
    RatingCheck = {"1-EXCELLENT", "2-GOOD", "3-FAIR", "4-POOR", "5-UNSERVICEABLE"},
    replRating = (x, y, z) as text => if List.Contains(RatingCheck, x) then x else RatingCheck{2},
    Source = Table.FromList( RatingCheck & {"Other"}, Splitter.SplitByNothing(), type table [Rating=text]),
    ReplValue = Table.ReplaceValue( Source, each [Rating], null, replRating, {"Rating"})
in
    ReplValue

In your production file you can replace my hard coded list with this:
RatingCheck = List.Buffer(TableCR[RatingCheck])

You can use the AI tooling to break down the code :wink:
I hope this is helpful

5

@Melissa Thank you so much for looking at it & that makes so much more sense now. Sometimes it’s just nice to learn where you went wrong even though a workaround was available. This is excellent, thank you. More study to do on lists within M I think! Really appreciate your response :smile: