Advanced Contain Function

[ayushmittal22]

Hi, 
I have E-commerce sales data with product name and price. 
I want to categorise the sales into different categories. 

I also have a separate list where categorization is given. For Ex- 1. Hair Comb- Hair care products 2. Lipstick- Lip Care Products

I want Power BI to read the Product Name and see if that product name contains any of these words. And then put its category beside it in a different column. 

I have a table with product names- such as - LOREAL HAIR COMB- BLUE COLOR

I want Power BI to check if Hair Comb is there in any product name and then put its category beside it. 

Have tried LOOKUP Value but doesn't seem to work in this case.

Please help:
@amitchandak @FreemanZ  @Mahesh0016 @bolfri 

[ayushmittal22]

Thanks Mahesh for your reply.

https://redseerconsutling-my.sharepoint.com/:x:/g/personal/ayush_m_redseerconsulting_com/EV-bnsUDvQN...


You can download it from here.

[Mahesh0016]

@ayushmittal22 
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.