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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
eugeneleefl
New Member

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

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!

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

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

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

6 REPLIES 6
v-huizhn-msft
Employee
Employee

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"

1.PNG

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

2.PNG

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

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"
Specializing in Power Query Formula Language (M)
MFelix
Super User
Super User

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

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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?

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.

Specializing in Power Query Formula Language (M)

Hi MarcelBeug

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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