Lookup for a string in a different Table

[JSIQUEI-YYC-ENB]

Hi there,

Needing a help with lookup value.

I have table 1 and Table 2 as shown below. 

I need a new calculated column in Table 1 called [New Category]

I'd need Power BI to search the string 'Key word' column in Table 2 into column [WBS Description] Table1. 

If the string is found within 'WBS Description', the column 'New Category' will be populated with the value in 'Categry' column in Table 2.

Ex: the string 'Eng' in Table 2 is found in the 'WBS Description' collumn, so, the value in 'New Category' will filled as Engineering. 

Hope I made myself clear. 

thanks!

[JSIQUEI-YYC-ENB]

Thaks for helping Greg.! by the way, I have a copy of your book on my shelf..that's a great source. cheers. 

[Greg_Deckler · Accepted solution]

@JSIQUEI-YYC-ENB Try this:

New Category = 
    VAR __Table = 
        ADDCOLUMNS(
            'Table 2',
            "__Found", IF( CONTAINSSTRING( [WBS Description], [Key Word]), [Category], BLANK() )
        )
    VAR __Result = MAXX( FILTER( __Table, [__Found] <> BLANK() ), [__Found] )
RETURN
    __Result