How to convert numeric scores to letter grades in Power BI Desktop?

[eugeneleefl]

I have a set of numeric scores from a test (e.g. 10, 30.5, 90.6 etc) and I wish to add a new column in Query editor that changes these numbers to letter grades. The conversion rule is as follows:

A: 70 to 100

B: 60 to 69

C: 50 to 59

😧 40 to 49

E: 39 and below

This there an efficient way to do this? I have multiple rows to convert. Thanks!

[v-huizhn-msft]

Hi @eugeneleefl,

I create sample table and get expected result. In Query Editor, please click "custom column" under "Add column", you will get the following navigation, please type the statement in the box.

=if[number]<=39 then "E" else if [number]<=49 then "D" else if[number]<=59 then "C" else if [number]<=69 then "B" else "A"

Click "Apply" on Home page, you will get expected result as follows.



Here is my Power Query statement.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjJWitWJVjIxBVOmBhDKAkyZQShziJwFhGdpqRQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [number = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"number", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if[number]<=39 then "E" else if [number]<=49 then "D" else if[number]<=59 then "C" else if [number]<=69 then "B" else "A")
in
    #"Added Custom"

Best Regards,
Angelia

[MarcelBeug]

From your example data it looks like you have decimal values Hence values like 39.5 have no matching category, according to your definitions.

Probably you should replace <=39 by <40, etcetera.

Edit: this would be my Power Query solution:

let
    Source = Table1,
    #"Added Custom" = Table.AddColumn(Source, "Grade", each Text.At("EDCBA",List.Max({0,List.Min({4,Number.RoundDown([Score]/10,0)-3})})), type text)
in
    #"Added Custom"

[MFelix · Accepted solution]

Hi @eugeneleefl,

Try to add a column with the following formula:

Grades Letters =
SWITCH (
    TRUE (),
    Table[Grade] <= 100
        && Table[Grade] >= 70, "A",
    Table[Grade] <= 69
        && Table[Grade] >= 60, "B",
    Table[Grade] <= 59
        && Table[Grade] >= 50, "C",
    Table[Grade] <= 49
        && Table[Grade] >= 40, "D",
    "E"
)

Regards,

MFelix

[eugeneleefl]

Dear MFelix and v-huizhn-msft

Thank you for your solution. Just a further complication: I have 100+ columns that I need to convert. Is there a way to do this more easily in either DAX or in Power Query?

[MarcelBeug]

If my contributions are still appreciated, I could think of a Power Query solution.

My question would be if you want new columns or just change the existing columns.

[eugeneleefl]

Hi MarcelBeug

I am keen to know how to change the existing columns, since the earlier posts already mentionedhow to insert new ones.