cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Sayth Regular Visitor
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.

 

idlocation  Nameworking outanswer_id
81483RandwickCummingsJamesJames Cummings81483 + 17 + 0 +138148317013
81483MenahCummingsJamesJames Cummings  
20602948RosehillCummingsJamesJames Cummings  
20602948Agnes Banks/HawkesburyCummingsJamesJames Cummings  
20602948Warwick FarmCummingsJamesJames Cummings  
20602948Agnes BanksCummingsJamesJames Cummings  
1 ACCEPTED SOLUTION

Accepted Solutions
Phil_Seamark Super Contributor
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   

image.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

1 REPLY 1
Phil_Seamark Super Contributor
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   

image.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!