Advanced Calculated Column Evaluation with Variable Values, Keys, Operators, and Operands

[Anonymous]

Is it possible to write something in M Query that evaluates a score based on the distinct key in the same row as the score against a key table that shows all of the associated operators and operands and the resulting value?

This is the logical evaluation table I put together in excel. 

So if there is key value and score value in a different table, can M Query take the key and score values, lookup to get the associated parameters that match the key in the table below?

Example: 

Key	Score
A2	4

If I run the above score against the matching row in the table below, the result should be 1 (Green Result).

Key

Score

Red Operator

Red Operand

Red Result

Key

Score

Yellow Min Operator

Yellow Min Operand

Key

Score

Yellow Max Operator

Yellow Max Operand

Yellow Result

Key

Score

Green Operator

Green Operand

Green Result

if

[Key]

=

A1

and

[Score]

>=

1

then

3

else if

[Key]

=

A1

and

[Score]

>=

0

and

[Key]

=

A1

and

[Score]

<=

0

then

2

else if

and

[Key]

=

A1

and

[Score]

=

0

then

1

[Key]

=

A2

and

[Score]

>=

8

then

3

else if

[Key]

=

A2

and

[Score]

>=

5

and

[Key]

=

A2

and

[Score]

<=

6

then

2

else if

and

[Key]

=

A2

and

[Score]

<

8

then

1

[Key]

=

A3

and

[Score]

>=

8

then

3

else if

[Key]

=

A3

and

[Score]

>=

70

and

[Key]

=

A3

and

[Score]

<=

79

then

2

else if

and

[Key]

=

A3

and

[Score]

<

8

then

1

[AlB · Accepted solution]

Hi @Anonymous 

Is the lookup table a 1-row only table? If so

if Table1[Key]{0} = "A1" and Table1[Score]{0} >= 1 then 3 else if Table1[Key]{0} = "A1" and etc....

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers