Forum Discussion

kokoro_1202's avatar
kokoro_1202
Helper I
5 years ago
Solved

help on conditional formatting or nested if

hi all I have a list of customer id from the system which is not accurate, is it possible to achieve the following with power bi as a new column? Any advice is appreciated.   Thank you so mu...
Need Help
  • themistoklis's avatar
    themistoklis
    5 years ago

    kokoro_1202 

     

    Try the following formula:

    New Column = 
SWITCH( TRUE(),
LEFT(Table[CustomerID],1) = "w" , Table[CustomerID],
LEFT(Table[CustomerID],3) = "000" , "u" & Table[CustomerID],
LEN(Table[CustomerID]) = 3 , "z" & Table[CustomerID],
"Other"
)
kokoro_1202's avatar
kokoro_1202
Helper I
5 years ago

themistoklis 

how shoulld the formula be to "a" to replace 000 i tried replace but it doesnt work?

Replace = (Table[CustomerID],1,3,"a"),
could you advice me again?

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi kokoro_1202 

This might help your requirement 

Result = If ( Len(Cust[CustomerID]) = 3 , "Z" & Cust[CustomerID] , IF( LEFT(Cust[CustomerID],3) = "000" , "U"&SUBSTITUTE(Cust[CustomerID],"000","a"),Cust[CustomerID]))