Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Calculated Column - Product Categories

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

 

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

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

3 Replies

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Community Champion
      8 years ago

      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's avatar
        Anonymous
        Not applicable
        8 years ago

        Absolutely brilliant.

         

        Many thanks

         

        Herbz09