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