cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## 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
1 ACCEPTED SOLUTION

Accepted Solutions
Super Contributor

## Re: create custom column - id substituting letters to corresponding number

Hi @Sayth

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   ```

Proud to be a Datanaut!

Super Contributor

## Re: create custom column - id substituting letters to corresponding number

Hi @Sayth

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   ```