Power Query (if text contains items from a list and return the item)

[castilian07]

Hi everyone, I am looking the following situation. I have used M Text.Contain formula on simple if then formulas in power pivot. In regular excel I successfully used an array formula for this situation but wanted to go a step further and solve it in power pivot. This is just a short list as the actual list is 30 projects plus more projects will be added over time.

Power Query Table 1	Table 2 Column 1	Table 2 Column 2	I want to search Table 1 for the variable in the first column in table 2 and return the value.	My main goal is to return the value in Column 2 to categorize the projects to their full names.
Tool Service Shel	Shel	Shell Projects	Example: Search "Shel" in table 1 and return Shel.	Example: Search "Shel" and return Shell Projects in Table 2.
Labor Service Exx	Exx	Exxon Projects
Hammer Tool Chev	Chev	Chevron Projects
Casings Chev

 

[castilian07]

Correction: Main goal example: Search "Shel" and return Shell Project from Table 2 in Table 1.