Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to 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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
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"
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsDear 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.
Hi MarcelBeug
I am keen to know how to change the existing columns, since the earlier posts already mentionedhow to insert new ones.
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |