If any of 6 given columns return a "No" value, give a No in Custom Column

[HarryClark]

Hi all, 

Hope everyone is well and looking forward to the weekend. 

I have an issue, where I have a table/query in PowerBI that contain 67 columns. 6 of those columns return a Yes or No based on whether a criteria in an assessment is met. I need to create a custom column, lets say "Is the place functional?", that returns a "No" value if any of those 6 columns in that row contain a "No" as an answer to their criteria. 

Now, in excel I would format the formula as:

=IF(COUNTIF(R16:W16,"No"),"No","Yes")

I am not greatly familiar with PowerQuery or DAX expressions so I was just wondering how I could manage this, I am at a complete loss and would really appreciate any help!! 

[Vijay_A_Verma · Accepted solution]

Another alternative approach for a custom column

You will need to replace [Column4], [Column6], [Column7] with you six column names.

= if List.Contains(Record.ToList([[Column4], [Column6], [Column7]]), "No") then "No" else "Yes"

[HarryClark]

This worked perfectly, thank you so much!

[AlienSx]

Hi, @HarryClark 

let
    // list of "given column" names
    given_columns = {"one", "two", "three", "four", "five", "six"},
    // replace your_table with a ref to your actual table
    result = 
        Table.AddColumn(
            your_table, "Is the place functional?", 
            (x) => 
                if List.Contains(
                    Record.FieldValues(Record.SelectFields(x, given_columns)),
                    "No"
                ) then "No" else "Yes"
        )
in
    result

[HarryClark]

Thank you! This looks interesting though more complex than the other solution, I am going to give it a try and see as well though.