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
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!
Solved! Go to 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"
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"
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
58 | |
53 | |
26 | |
16 | |
10 |