Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Conditonal Column based on a string

Morning All

 

Im sure there is an easy way to do this.

 

Maybe using a switch function as that can cater for blanks?

 

I have a table that will have data like that in Table 1 Column 1

 

I want to add a new conditional column 2 which will do the following:

 

If it finds the String ITC then in the new conditional column it will output IT Component

If it finds the String APP then in the new conditional column it will output IT Application

 

Excel Mock Up

 

 

 

 

Table 1 – Current

 

Data Source
APP-51059
APP-51026
APP-51037
APP-51040
ITC-92620
ITC-92338
ITC-92828

 

 

Table 2 – Required

 

Data SourceNew Conditional Column
APP-51059IT Application
APP-51026IT Application
APP-51037IT Application
APP-51040IT Application
ITC-92620IT Component
ITC-92338IT Component
ITC-92828IT Component

 

 

Any pointers appreciated.

 

Jimmy

 

Need Help
  • dk_dk's avatar
    dk_dk
    2 years ago

    Hi Anonymous ,

    You can do this in Power Query as well with a conditional column, but if you want to add a DAX calculated column you can try this:

     

    Conditional Column = 

SWITCH(TRUE(),
CONTAINSSTRING(Source[Data Source],"APP"), "IT Application",
CONTAINSSTRING(Source[Data Source],"ITC"), "IT Component",
"Other")


    You can replace "Other" with just "" if you want to show blanks on no match, and you can also expand the switch statement to search for additional strings and display a different result.

     

2 Replies

  • dk_dk's avatar
    dk_dk
    Super User
    2 years ago

    Hi Anonymous ,

    You can do this in Power Query as well with a conditional column, but if you want to add a DAX calculated column you can try this:

     

    Conditional Column = 

SWITCH(TRUE(),
CONTAINSSTRING(Source[Data Source],"APP"), "IT Application",
CONTAINSSTRING(Source[Data Source],"ITC"), "IT Component",
"Other")


    You can replace "Other" with just "" if you want to show blanks on no match, and you can also expand the switch statement to search for additional strings and display a different result.

     

    • dk_dk's avatar
      dk_dk
      Super User
      2 years ago

      And here is how you would need to configure a conditional column in Power Query instead:

      Hope this helps.