Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

Group By but with conditional formula

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.

Kit_0-1655787132189.png

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

Kit_1-1655787337414.png

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

 

Thank you for reading my post!

 

 

1 ACCEPTED SOLUTION
Vijay_A_Verma
Super User
Super User

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"

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

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

Vijay_A_Verma
Super User
Super User

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"

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors