Assigning numerical value to school year and reg groups

[TBoyle]

Hi, I want to add a numerical value to year groups and registration groups. As it doesn't like nursery and reception labeling.

For example:

Year group column

00N = -1

00R = 0

1 =  1

etc

Reg group column

F3 = 7

L4 = 8

etc

Also is there a way to tell a column with blanks to show a certain word, for instance, I'd like my blank cells to display 'unknown'.

Thank you in advance for any help you may be able to give.

[ray_aramburo]

Create a new column with DAX (or conditionally through Power Query). With DAX it will be something similar to:

Year group column = SWITCH( TRUE (), TableName[ColumnWithYourValues] = "00N", -1, TableName[ColumnWithYourValues] = "00R", 0, TableName[ColumnWithYourValues] = "1",TableName[ColumnWithYourValues]= BLANK(), "Unknown")

Just be careful that if you use the Unknown label, your numerical values (-1,0,1, etc.) must be string format.

Any additional condition you can write it in the Switch statement, the syntaxis would be Expression (Column Name evaluated against something), Result, ...)