Student Grades from Changing values in Excel source Sheet

Power Query
1

Hi,
I tried many ways to add student letter grades by taking the user input in Excel Table as per his/her desire. But at last, I have to add conditional column in Power Query for letter grades. I am avoiding the situation due to non-flexibility of it. To calculate GPA points I merged the Gradelist with the Result table. However, by altering the grading policy in this table it doesn’t reflect in students grades.

The requirement is to give the option to users for their own grading scheme in excel table. Only Power Query solution is required. Power BI DAX and Excel Formulas solutions are not required.
test-Grades.pbix (53.6 KB)
test File.xlsx (28.7 KB)

2

Hi @Tanzeel,

See if this meets your requirement.
First the GradeList query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LY9JDoMwEAT/4mswMvEy5AhE+YTl/38jU605dAtVUyOYM5W0pfrx+nmKsraZGo8N/vUcCrzDe/N6MnC3aI2dEenxvBX4QBqSXsC9RmtEGkh3FrZoRsM0zJvvVMSRTBIXqy7WuHginUhXFrZojZhH4ZWLX1TW+gM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Min = _t, Max = _t, Grade = _t, GPA = _t, #"Grade Point" = _t]),
    ChType = Table.TransformColumnTypes(Source,{{"GPA", type number}, {"Grade Point", type number}, {"Min", Int64.Type}, {"Max", Int64.Type}}),
    AddGetFields = Table.AddColumn(ChType, "GetFields", each [ Grade = [Grade], GPA = [GPA]])
in
    AddGetFields

.
and second the Grades query

let
    MarkList = List.Buffer( GradeList[Max] ),
    RecList = List.Buffer( GradeList[GetFields] ),
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bVZLliQhCLxLrrv7pUD6OcAs+wT96v7XmBJEA62NWUWI/AL07+9K9fv333e6vi563gvTT/80XRN18f1zAHLAqpzkvZSFSV9S0V0dadfryy1m1WJTbn1fMsUEh4Kcd+yx7eYKL10Z5+hS9Xd5wLAqz3jTDaZbkNKOkMfF4Fa2DSqq7yX3+Jc1Kn6m/urn1OmASyRIaZ1owVWNSmUYc8Z0PrzCpbKyMQqZN3Gocp5hj3IAyBXsqtUc4nuypsf23njq7RpBLBtI2XkDCBcXjohyC0ZzWUY9XSl8TkQOfJyt4QtU1HwbdTfzaDwZqc04OF3BMojNiXq6ZR+aIIvTy+t7RybdQH5eBka6yyZmrIWCI3SedkfVntWwmovMwa6U5TQtdwsmCOS8Y2lr4hKsBqe2iEUmOxNGA60QADpgs00RG3y0gM2NpyK7GgRW5olD69nkcmK0iu1EgAqPwAex0XDJs1aEetAtQU4BczrbFpk8SItRk1hotH2mc0MNBPgALeEb55igDEqrFIpbVVU5/WBQDRIZADlgmEo0IM4rBSmfzdvIWcFxYNByNCK8oVZ5wZlCY1LCJr3uJATcFpqhtnEaAMKfcC2reO2HYXTxw8BqerX0TWRo/1+RhSAXHFcDRVoBLPeRunAlJZt/5EcU7GE4ukC4EVVieZP5tIKimWaWYBSoQXnRtuC8QIAOcA5igkknmOUObhd9SnATara9wmlxEeW8YzjWHOOQ4U/hVmiIOlsEbm2Q0obIctCfIDxox4vlOQWDbdIY7/LblfZRAQg8bObo8mabL8XtDkj2ABl3y+0BJMzIFNMBzRFybS8ADtPSLOMsTjJrQt4bGnlFCQdppOu1Hod4qfP1ev0H", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [RegNo = _t, #"CLO-2" = _t, #"CLO-1" = _t, #"CLO-4" = _t, #"CLO-3" = _t, #"PLO-4" = _t, #"PLO-1" = _t, #"PLO-5" = _t, #"PLO-3" = _t, #"PLO-2" = _t, P2 = _t, C1 = _t, C3 = _t, C2 = _t, C4 = _t, #"Final-Exam" = _t, Sessional = _t, #"Mid-Exam" = _t, TotalMarks = _t]),
    ChType = Table.TransformColumnTypes(Source,{{"CLO-2", type number}, {"CLO-1", type number}, {"CLO-4", type number}, {"CLO-3", type number}, {"PLO-4", type number}, {"PLO-1", type number}, {"PLO-5", type number}, {"PLO-3", type number}, {"PLO-2", type number}, {"P2", type number}, {"C1", type number}, {"C3", type number}, {"C2", type number}, {"C4", type number}, {"Final-Exam", type number}, {"Sessional", type number}, {"Mid-Exam", type number}, {"TotalMarks", type number}}),
    GetRecord = Table.AddColumn(ChType, "Temp", each 
        let 
            Mark = [TotalMarks], 
            GradeKey = List.RemoveNulls( List.Transform( MarkList, each if Mark <= _ then _ else null )){0},
            Position = List.PositionOf( MarkList, GradeKey ),
            GetRecord = RecList{Position}
        in 
            GetRecord
        ),
    ExpandRecord = Table.ExpandRecordColumn(GetRecord, "Temp", {"Grade", "GPA"}, {"Grade", "GPA"})
in
    ExpandRecord

.
Sample of the result

image

Here’s your sample file.
eDNA Grades.pbix (53.2 KB)

I hope this is helpful

2 Likes
3

Thanks, I will try it today.

Because, each table will be different for user. My primary concern is to avoid hard coded queries. For Grades and students marks, we will take user input in excel table.

For example, here are 12 PLOs, in different combination, which also alter C1, C2, etc. in every result. So, I need dynamic columns names as far as possible.