Calculated Column - Product Categories

[Anonymous]

Good evening All,

I am trying to create a calculated column that puts product codes into categories. A sample list of product codes are as per below.

The rules are as follows:

If the code contains the letter L and either an L or an E, then the category is LL. 

If the code contains the letter L and either an O or an R, then the category is LO.

Otherwise "No Category"

Can someone assist with a formula suggestion or recommend a solution. I thought this was quite tough as powerbi has to look for certain text within a string.

Product codes are anywhere between 3 characters to 5 characters long

Many thanks

Herb

[Anonymous]

https://www.dropbox.com/s/8u9isfkymeyz3on/Product%20Codes.xlsx?dl=0

[Zubair_Muhammad · Accepted solution]

HI @Anonymous

Try this

=
IF (
    OR (
        SEARCH ( "L", TableName[Product Codes], 2, 0 ) > 0,
        SEARCH ( "E", TableName[Product Codes], 2, 0 ) > 0
    ),
    "LL",
    IF (
        OR (
            SEARCH ( "O", TableName[Product Codes], 2, 0 ) > 0,
            SEARCH ( "R", TableName[Product Codes], 2, 0 ) > 0
        ),
        "LO",
        "No Category"
    )
)

[Anonymous]

Absolutely brilliant.

Many thanks

Herbz09