Detect No before Yes

[npals]

Hello,

I am trying to create a new measure or calculated column that will detect if "no" exists before "yes" within a row.  If this instance does exist, the value would equal "1" or "true", and if this instance does not exist within that row, the value would equal "no" or "false".  There are a total of 23 columns.  Other values would possibly exist within this row , like "N/A" for example, but these would not matter when it comes to the result of no before yes.  

Note:  The no would not need to exist directly before the yes.  For example, the row could read "No" "No" "Yes" "N/A" "No" "Yes"

Simply because a no reads before a yes within the row, the value would have to be "1" or "true".

Please help by providing a calculated column or a measure, thank you in advance.

[ImkeF · Accepted solution]

In the query editor, you add a column with this formula:

List.PositionOf(Record.FieldValues(_), "No") < List.PositionOf(Record.FieldValues(_), "Yes")

if you need a case-insensitve check, you transfer to lower like this:

List.PositionOf(Record.FieldValues(Text.Lower(_)), "no") < List.PositionOf(Record.FieldValues(Text.Lower(_)), "yes")