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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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

Top Solution Authors
Top Kudoed Authors