Group By but with conditional formula

[Anonymous]

Hi all, 

I'm at a loss with this problem and hoping your incredible brains can help me figure it out.

I am trying to achieve the equivalent of a Group By operation in Power Query, but using conditions rather than aggregation to get the new column value.

Here is some simple data to illustrate: It's a list of Student names, Subject they studied, and the Grade they got in each subject.

I would like to reduce this list to just the Student name and an Overall Grade. The Overall Grade is calculated like this:-

If a student studied less than 3 subjects: if they pass 100% of the subjects they get an overall grade of Pass, otherwise they get an overall grade of Fail

If a student studied 3 or more subjects: if they pass >50% of the subjects they get an overall grade of Pass,  otherwise they get an overall grade of Fail

I'm a bit stumped on how to achieve this in Power Query. Please give me some pointers.

Thank you for reading my post!

[Anonymous]

Hey, thank you @Vijay_A_Verma! This is a great solution.

[Vijay_A_Verma · Accepted solution]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUfIF4oDE4mKlWB2IgCu6QDCygBNUwC0xMwcu4Isu4IquxRFdwANZwBldizO6Lc7oLnWBGgpX4YLuDhd0M1zQbXGFCsBVuKL4NhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Student = _t, Subject = _t, Grade = _t]),
    #"Added Custom" = Table.AddColumn(Source, "NumGrade", each if [Grade]="Pass" then 1 else 0),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Student"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"NumGradeSum", each List.Sum([NumGrade]), type number}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Overall Grade", each if [Count]>=3 and [NumGradeSum]/[Count]>0.5 then "Pass" else if [Count]<3 and [NumGradeSum]/[Count]=1 then "Pass" else "Fail"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Count", "NumGradeSum"})
in
    #"Removed Columns"