Excel AND function, but, in power query

[lucassescato]

Hello guys, hope you're good!

I have the following formula in excel, and im trying get the same results in power query:

I tried to use the conditional column in Power Query, but it returned "OK" for all the conditions. This happened because if any of the tests returns "OK," it results in "OK" for all the tests. Is there any way to do this using the M language or something similar?

Thanks in advance and sorry for my bad english hehe

[jennratten · Accepted solution]

Hello - you could do it like this:

= Table.AddColumn(#"Changed Type", "Custom", each if List.AllTrue ( { [Column2]="OK",[Column3]="OK",[Column4]="OK",[Column5]="OK",[Column6]="OK" } ) then "OK" else "NOTOK", type text)

[lucassescato]

thank you!! it works

[Vijay_A_Verma · Accepted solution]

Use this in a custom column

if List.Contains(List.Range(Record.ToList(_), 1, 5), "NOTOK") then "NOTOK" else "OK" 

[jennratten · Accepted solution]

Hello - you could do it like this:

= Table.AddColumn(#"Changed Type", "Custom", each if List.AllTrue ( { [Column2]="OK",[Column3]="OK",[Column4]="OK",[Column5]="OK",[Column6]="OK" } ) then "OK" else "NOTOK", type text)