Add column based on a list from another table

[Anonymous]

I have two tables. Table 1 contains a list of keywords with the associated category. Table 2 contains a summary and description of work to be done. I want to calculate a column in Table 2 that will seach for values from the summary and description columns in Table 2 and then search the keywords column in Table 1 to see if any of the keywords are contained in the text. If found assign it the corresponding category for the keyword. If not, mark it Other.

Here is a simplified version of my data:

Table 1:

Keyword	Category
WIRING	Network
ROUTER	Network
PC	Workstation
VM	Server

Table 2: I'd like the results to populate in the Category column

Summary	Description	Category
NETWORK CABLING	THIS WORK IS TO REROUTE NETWORK WIRING	Network
ROUTER CONFIG	THIS IS TO CONFIGURE ROUTER 2	Network
WORKSTATION REIMAGE	REIMAGE BOB'S PC	Workstation
VM CONFIGURE	CONFIGURE VIRTUAL MACHINE	Server
BATTERY REPLACEMENT	REPLACE THE BATTERIES IN BOB'S MOUSE	Other

[AlB · Accepted solution]

Hi @Anonymous

Try this for your calculated column:

Category = 
VAR _ResultCategory =
    CONCATENATEX (
        VALUES ( Table1[Keyword] );
        IF (
            (
                FIND ( Table1[Keyword]; Table2[Summary]; 1; 0 ) > 0
                    || FIND ( Table1[Keyword]; Table2[Description]; 1; 0 ) > 0
            );
            LOOKUPVALUE ( Table1[Category]; Table1[Keyword]; Table1[Keyword] )
        )
    )
RETURN
IF(LEN(_ResultCategory)=0;"Other";_ResultCategory)

Code formatted with  

[Anonymous]

Works perfectly. Thanks!