Convert QPR expression code to Power Query

alice11987 · ‎02-28-2023

Hi, I would like to replicate the similar conditional column in Power Query, How should I do it?

let twomillion = ["CD1009", "CD1011", "CD1012", "CD3007", "CD3008", "CD3010", "CD3013", "CD3015", "CD3016", "CD3028", "CD4001", "CD4002", "CD4004", "CD4005", "CD4008", "CD5003", "CD5009", "CD5010", "CD6001", "CD6002", "MC1003", "MC1004", "MC1005", "MC1006", "MC1009", "MC1010", "MC1012", "MC1014", "MC1015", "MC1017", "MC1018", "MC1019", "MC1020", "MC1022", "MC1023", "MC1024", "MC1025", "MC1026", "MC1027", "MC1028", "MC1029", "MC1033", "MC1034", "MC2001", "MC2002", "MC2003", "MC3007", "MC3009", "MC3010", "MC3011"];


let twohundredthousand = ["CS2013", "CS5008", "CS6001", "CS6002", "CS6003", "CS6004", "CS6005", "MK1006", "MK1013", "MK5005", "MK6002", "MK6003", "RD1049"]

let fivehundred = ["CS4002", "CS4003", "CS4004", "CS4005", "CS4006", "CS4010", "CS4011", "CS4012", "CS4013", "CS4015"];

let others = ["CD5008", "CS2021", "CS4016", "CS4017", "MC3001", "MC3002", "MC3004", "MC3005", "MK3002", "MK3011", "MK5003", "MK5004", "RD1024", "RD1027", "RD1029", "RD1030", "RD1031", "RD1032", "RD1034", "RD1035", "RD1036", "RD1039", "RD1041", "RD1042", "RD1043", "RD1044", "RD1045", "RD1048", "RD1051", "RD2016", "RD2019", "ZZ1001", "ZZ1002", "ZZ1003", "ZZ1011"]

if(Attribute("Risk Allocation")=="Medium risk") { 
if( (Attribute("Contract Policy Type")=="L2 - Simple" 
 && (
Attribute("Contract Value") >= 2000000 && Attribute("Spend Category").In(twomillion )  ||
Attribute("Contract Value") >= 200000 && Attribute("Spend Category").In(twohundredthousand ) ||
Attribute("Contract Value") >= 500000 && Attribute("Spend Category").In(fivehundred) ||
Attribute("Spend Category").In(others) 
)) || (Attribute("Contract Policy Type")=="L3 - Complex" || Attribute("Contracting Guidelines") == "An agreement must be used regardless of the amount.")

)
{"Compliant"}else{" Non-Compliant"} 
}
else if(Attribute("Risk Allocation")=="Low risk")
if(Attribute("Contracting Guidelines")=="For this nature of spend, create a Purchase Order.")

{" Non-Compliant"}else{""}

In my table, there are following columes:

Spend Category

Risk Allocation

Contracting Guidelines

Contract Policy Type

Contract Value

I would like to create a colume to indicate whether the row of the contract whether is it "Compliant" or "Non-compliant"

Thank you