CountIfs for columns in Power Query

[DrDebit]

In Power Query, is it possible to add a custom column to count the number of times in each row that a number is over 80? For example, I have exam scores and I want to count how many scores are over 80 for each student:

Student Score1 Score 2 Score 3 Score4 Count
Student 1 80 90 75 85
Student 2 79 92 45 85
Student 3 65 64 15 70

So, the students would have a sixth column (Count) with the following numbers: 3, 2, 0, respectively.

Thank you.

 

[GraH]

a simple solution could be

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Student"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Student"}, {{"Details", each _, type table [Student=text, Attribute=text, Value=number]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Scores", each [Details][Value]),
    #"Added Custom" = Table.AddColumn(#"Added Custom1", "Scores Above 80", each List.Count(List.Select([Scores], each _>= 80))),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Details"}),
    #"Extracted Values" = Table.TransformColumns(#"Removed Columns", {"Scores", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Scores", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Scores.1", "Scores.2", "Scores.3", "Scores.4"})
in
    #"Split Column by Delimiter"

 

[DrDebit]

Thank you so much. It seems like there should be one function just to do a simple COUNTIF. Oh well. I really appreciate your efforts. Thank you!!!!

 

[GraH]

Most welcome. Remember PQ is designed for structured input. It loves to have values in the same column. Hence my unpivot step. However you could use records as well.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Student", type text}, {"Score1", Int64.Type}, {"Score2", Int64.Type}, {"Score3", Int64.Type}, {"Score4", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Count Scores Above 80", each 
    List.Count(
        List.Select(
            Record.ToList(
                Record.RemoveFields( _ , "Student")
            )
        , each _ >= 80 )
        )
    )
in
    #"Added Custom"

 

[DrDebit]

Thank you so much, again!!