How to modify my expression to make it easy add new code for recode ?

[admin11]

Hi All

All my below code is working fine , but not easy to maintenance , when i need to add new code for recode. 

_Pro Clas D =

SUBSTITUTE(

SUBSTITUTE(

SUBSTITUTE(

SUBSTITUTE(

SUBSTITUTE(

SUBSTITUTE(

SUBSTITUTE('INVC'[PRO_CLASS_CODE],

"2200","BK BUS TERMINAL"),

"2209","BK ETHERCAT"),

"2207","BK DRIVE"),

"2208","BK EMBEDDED PC"),

"2203","BK FIELD BUS CARD"),

"1234","CL"),

"1234","RW")

Can some one share with me how to simplify the code , so that when i add new pro_class_code , i don't need to add :-

SUBSTITUTE(

My PBI file :-

https://www.dropbox.com/s/gfvvhq0mmuo0uyo/PBT_V2021_400%20INVC_TI_.pbix?dl=0

PAul

[Sumanth_23 · Accepted solution]

hi @admin11 - A better approach would be to maintain the code to desription mapping in a DIMENSION table and perform a lookup and ro derive the description for the code from the DIMENSION table. This was when a new code comes in you need to just add it to the DIMENSION table and all records for the corresponding code will have the new value. 

Please vote / mark this response as a solution if this helps you. This will also help other community members who may have a similar question. 

Happy to help!  

[admin11]

@Sumanth_23 

Yes , that is right approach. Thank you

[Sumanth_23]

@admin11 - Happy to help! 

[daxer-almighty]

It's easy when you do it where it should be done: in Power Query. Data wrangling should never be done in DAX since DAX has a very different purpose in life. DAX = Data Analysis eXpressions. To do data wrangling you should use M---the language that Power Query speaks. If you do that, the maintenance cost will be minimal.