Does a column contain text from another column?

[Anonymous]

Hello! If I have two tables:

Table 1:

Comment

I like to eat hot dogs

I don't like pineapple

A red car

Table 2:

Vehicle	Food
car	dog
	apple

How could I use the columns in Table 2  to create a new column in Table 1 like so:

Comment	Category
I like to eat hot dogs	Food
I don't like pineapple	Food
A red car	Vehicle

where the keywords in the Table 2 are searched for in the Comment column in Table 1, and the correct category is assigned? In the query editor.

[amitchandak]

@Anonymous ,

Try a new column like this in table 1

new column =
var _vh = sumx(filter(Table2,SEARCH(Table[Vehicle],Table[Comment],,0)>0,1,0)+0
var _food = sumx(filter(Table2,SEARCH(Table[Food],Table[Comment],,0)>0,1,0) +0
return
if(_food >0, "Food","Vehicle")

[Anonymous]

@amitchandak even if I try to use DAX, it is unclear to me how I could use the columns from two seperate tables in this way as they are unrelated. So I couldn't mix and match the columns like this. If DAX is the easier way to solve this problem I would be happy to go with it.

[v-kelly-msft · Accepted solution]

Hi  @Anonymous ,

First unpivot table2,and you will see:

Then create a measure as below:

Category = 
var _comment=SELECTEDVALUE(Table1[Comment])
var _value=CONCATENATEX(FILTER(DISTINCT('unpivot-Table2 (2)'[Value]),CONTAINSSTRING(_comment,[Value])),[Value],",")
Return
CALCULATE(MAX('unpivot-Table2 (2)'[Attribute]),FILTER('unpivot-Table2 (2)','unpivot-Table2 (2)'[Value]=_value))

And you will see:

For the related .pbix file,pls see attached.

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

@amitchandak thank you for the possible solution. However, what if I have many more categories, and these categories could change as it is based on a user input excel file? The format of table 2 can change. It is just an example structure that i put in. Maybe its better to have it pivoted.

[amitchandak]

@Anonymous , then I will think to unpivot the second table and try a solution.

https://radacad.com/pivot-and-unpivot-with-power-bi

[Anonymous]

@amitchandak even if I pivot, I am unsure what the solution would be. I am looking to do this in PowerQuery not in DAX. Is this possible? Thank you.