Forum Discussion
Solved
Lookup for a string in a different Table
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 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
2 Replies
Thaks for helping Greg.! by the way, I have a copy of your book on my shelf..that's a great source. cheers.
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
