cancel
Showing results for
Did you mean:
Helper I

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

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
1 ACCEPTED SOLUTION
Super User III

Hi @kbig02

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

Super User III

Hi @kbig02

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

Announcements