create custom column - id substituting letters to corresponding number

[Anonymous]

I want to create a custom column from 2 columns.

one column is a number that is an id of a person but not unique when location included. So I want to construct an id using the existing id and concatenating the numeric ascii representation of the first three letters of the location.

So a worked example on data is below.

id	location			Name	working out	answer_id
81483	Randwick	Cummings	James	James Cummings	81483 + 17 + 0 +13	8148317013
81483	Menah	Cummings	James	James Cummings
20602948	Rosehill	Cummings	James	James Cummings
20602948	Agnes Banks/Hawkesbury	Cummings	James	James Cummings
20602948	Warwick Farm	Cummings	James	James Cummings
20602948	Agnes Banks	Cummings	James	James Cummings

[Phil_Seamark · Accepted solution]

Hi @Anonymous

The following DAX calculated column looks pretty good.

Column = 'Table3'[id] & 
           UNICODE(UPPER(MID(Table3[Location],1,1))) - 65 &
           UNICODE(UPPER(MID(Table3[Location],2,1))) - 65 &
           UNICODE(UPPER(MID(Table3[Location],3,1))) - 65