Forum Discussion
Add column based on a list from another table
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 |
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)
2 Replies
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)