Conditionnal Column

[Anonymous]

Hello, i have a table like this.

The column that we need here to create the personnalized column are :

Criteria1_A
Criteria1_B
Criteria1_C

Criteria1_D
Criteria1_E

So in the personnalized column i want to display (in Power query):

OK : 

If all the five criterias are equals to 0 or are null (can be one criteria = 0 and others are null)

Same :

If all the five criterias are the same or some are null (not all null because if all null then it should display  OK)  and all the others are the same (but not equal to 0)

Different :

If all the five criterias are not the same or some are null (not all null because if all five criterias are null then it should display  OK)  and all the others are not the same (but not equal to 0)

Optimization :

If there is atleast one criteria equal to 0 (but not all because if all five criterias equal 0 then it should be OK) and atleast one of all the remaining criterias is not null

I'm sorry if i was not clear (you can check the exemple to make it clearer).

Thank you

[Vijay_A_Verma · Accepted solution]

Use below formula in a custom column

= [l=List.Distinct({[Criteria1_A],[Criteria1_B],[Criteria1_C],[Criteria1_D],[Criteria1_E]}),
Result = if List.IsEmpty(List.Difference(l,{0,null})) then 
"OK" else 
if List.Count(List.RemoveNulls(l))=1 then "Same" else
if List.Count(List.Select(List.RemoveNulls(l),each _=0))>0 and List.Count(List.RemoveItems(l,{0,null}))>0 then "Optimization" else "Different"
][Result]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLAwLE60UpGQBYKAgkaoymFiZsAGYYGBlhJsAJT7FJQWTMgw9QARiBJmKOJQYUtsJoGMwGkwhKHHNQAQ6wyMElDVElkKSOsNiPMNcZpL9BdsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product ID" = _t, Criteria1_A = _t, Criteria1_B = _t, Criteria1_C = _t, Criteria1_D = _t, Criteria1_E = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product ID", Int64.Type}, {"Criteria1_A", Int64.Type}, {"Criteria1_B", Int64.Type}, {"Criteria1_C", Int64.Type}, {"Criteria1_D", Int64.Type}, {"Criteria1_E", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Personalized Column", each [l=List.Distinct({[Criteria1_A],[Criteria1_B],[Criteria1_C],[Criteria1_D],[Criteria1_E]}),
Result = if List.IsEmpty(List.Difference(l,{0,null})) then 
"OK" else 
if List.Count(List.RemoveNulls(l))=1 then "Same" else
if List.Count(List.Select(List.RemoveNulls(l),each _=0))>0 and List.Count(List.RemoveItems(l,{0,null}))>0 then "Optimization" else "Different"
][Result])
in
    #"Added Custom"

[Anonymous]

Hello, thank you it works great.

I learned some news functions thanks to you

Have a nice day!