help on conditional formatting or nested if

[kokoro_1202]

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 much!

[Anonymous]

1. Create a new column for Result field

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

2. Create an another column for font formatting.

FontId = If ( Len(Cust[CustomerID]) = 3 , 1 , IF( LEFT(Cust[CustomerID],3) = "000" , 2 ,3))

3. Go to format pane and select Value "Result" from Conditional Formatting pane and enable Font color and follow as per the below screenshot

[themistoklis · Accepted solution]

@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]

@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]

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]))

[themistoklis]

The formula should be

Column = REPLACE(Table[CustomerID],1,3,"a")

[kokoro_1202]

@themistoklis 

Tried the following, but it say "Function 'SWITCH' does not support comparing values of type True/False with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values."

New Column =
SWITCH( TRUE(),
LEFT(Table[CustomerID],1) = "w" , Table[CustomerID],
REPLACE(Table[CustomerID],1,3,"a"),
LEN(Table[CustomerID]) = 3 , "z" & Table[CustomerID],
"Other"
)

[themistoklis]

@kokoro_1202 

Try this:

New Column = 
SWITCH( TRUE(),
LEFT(Table[CustomerID],1) = "w" , Table[CustomerID],
LEFT(Table[CustomerID],3) = "000" , REPLACE(Table[CustomerID],1,3,"a"),
LEN(Table[CustomerID]) = 3 , "z" & Table[CustomerID],
"Other"
)