Power BI DAX: String manipulation--> how to search multiple values and return a mapping value

[zakkyang]

0

For example, I have a column of web site:

www.google_b.com
www.amazon_1-3b.com
www.amazon345.com

I know that google and amazon are the ones I want to retrieve for another column.

I want to have this column as below

www.google-a.com                google
www.google_b.com                google
www.amazon_1-3b.com             amazon
www.amazon345.com               amazon

I will have a list of the desired keywords to map with like [google, amazon, facebook, etc] Does anyone know how to get the dax formula?

It is more like a LIKE "%xyz%" syntax in SQL.

Thanks.

[tex628]

Hi @zakkyang , 

I'm thinking you should be able to use something along the lines of this:

Column = List.First(List.FindText({Keyword list}, [Web site column]))

List.FindText - PowerQuery M | Microsoft Docs
List.First - PowerQuery M | Microsoft Docs

How does the output look? 

Br, 
J

[zakkyang]

Hi J,

Cannot find the List.First function, is it power query or dax?

[tex628]

Power Query! 

[zakkyang · Accepted solution]

Thanks J. I found a solution using SWITCH.