Advanced Calculated Column Evaluation with Dynamic 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

[PhilipTreacy]

Hi @Anonymous 

Please download this sample PBIX file with code

I don't have any sample data to use so I just made some up.  If you provide your actual data I can test the code against it.

Copy/paste this M code into a blank query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjRU0lEyUIrVATKNgExDCNMYyDRSio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t, Score = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Key", type text}, {"Score", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Key] = "A1" then

   if [Score] >= 1 then 3 

   else if [Score] >= 0 and [Score] <= 0 then 2 

   else if [Score] = 0 then 1

   else 

   if [Key] = "A2" then

	  if [Score] >= 8 then 3 

	   else if [Score] >= 5 [Score] <=	6 then 2 

	   else if [Score] < 8 then 1

       else 

       if [Key] = "A3" then

	      if [Score] >= 70 and [Score] <= 79 then 2 

	      else if [Score] >= 8 then 3 

	      else if [Score] < 8 then 1	

          else null else null else null else null)
in
    #"Added Custom"

NOTE: Double check this line else if [Score] >= 0 and [Score] <= 0 then 2  because your logic means that this is the same as [Score] = 0 which already has a line to evaluate for this.

Regards

Phil