Forum Discussion
create custom column - id substituting letters to corresponding number
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 |
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
1 Reply
- Phil_Seamark
Microsoft Employee
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