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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register 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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors