If a field contains one of the keywords, return this keyword ?

[Katja_O]

Hi

I have a table with Item number and Item text, and another table with a list of Keywords. I want to check whether Item text contains one of the Keywords, and return this Keyword.
How do I do this?

Example:

Item nr Item text
1 Blue hgffhg
2 Red uuuu
3 PPP

Keywords table:
Blue
Red

I need to return a value "Blue" for item nr 1, and "Red" for item nr 2. For item 3 it should be blank.
The keywords table is much longer and consists of 100 keywords..

Any help would be appreciated!

Katja

[MattAllington]

This doesn’t sound like a DAX task, but maybe a power query task. The general pattern is to optimise and prepare your data before loading to power pivot rather than trying to solve such issues in DAX. Can you give a more detailed description of the problem you are trying to solve? Or you could use the FIND function in DAX.

[Katja_O]

Hi Matt

Thanks for your reply.

The detailed description of the problem:
I have a column with Item number, and a column with Item name.
The item name consists of 5-7 words, one of them is a Keyword.

I have another table with a list of Keywords.

I need to check for each Item name whether it contains one of the Keywords from the Keywords table. And as a result, I would like to return the Keyword text in a column next to Item name column.

I tried to use =SWITCH(TRUE(),SEARCH formula. It works okay if you have a limited number of Keywords. But I have several thousands of them, therefore I need to lookup in the Keywords table.

Hope this explains the problem.

Thank you in advance!

[MattAllington]

Ok, thanks. I see this as a data preparation task, better for power query rather than power pivot. You say each time text contains “one” keyword. Is it really 1 only, or can there be more than 1? It makes a difference.

[Katja_O]

Hi Matt,

There can be two words as well.

[MattAllington]

Ok, so this is not really a job for power pivot. This sounds like master data to me. There should be a one time creation of the key words for each product. You need a lookup table of all the products, and a data table of the product codes and key words, like this.

Product table

ID. Desc

1 Red Apple cider
2 Blue Bike

Key word table

ID. Keyword
1. Red
1. Apple
2. Blue
2. Bike

Etc

Then you need a many to many pattern to make it all work. It is actually quite complex, so I have produced a demo for you. Let me know if this is what you need.

keywords.xlsx

(223.84 KiB) Downloaded 31 times

[Katja_O]

Hi Matt

Thank you so much for your reply and demo.

The problem is I do not have a product ID in the Keywords table, so the table with the Keywords and Item name cannot be related.

Is it possible to extract the keyword from the item name without having this relationship?

[MattAllington]

You could join it to the description. It sounds to me like your data is unstructured, and you need some structure to it if you want to go down this path.

[Katja_O]

I will try to see what I can do.

Thank you for your help!