Forum Discussion

npals's avatar
npals
Frequent Visitor
8 years ago
Solved

Detect No before Yes

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.

 

  • 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")

     

1 Reply

  • ImkeF's avatar
    ImkeF
    Community Champion

    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")