Forum Discussion
Conditionnal Column
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
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"
2 Replies
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"