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
If a field contains one of the keywords, return this keyword ?
-
- Posts: 1019
- Joined: Sun May 04, 2014 4:01 pm
- Location: Sydney, Australia
Re: If a field contains one of the keywords, return this keyword ?
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.
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training
Power BI Training
Re: If a field contains one of the keywords, return this keyword ?
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!
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!
-
- Posts: 1019
- Joined: Sun May 04, 2014 4:01 pm
- Location: Sydney, Australia
Re: If a field contains one of the keywords, return this keyword ?
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.
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training
Power BI Training
Re: If a field contains one of the keywords, return this keyword ?
Hi Matt,
There can be two words as well.
There can be two words as well.
-
- Posts: 1019
- Joined: Sun May 04, 2014 4:01 pm
- Location: Sydney, Australia
Re: If a field contains one of the keywords, return this keyword ?
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.
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.
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training
Power BI Training
Re: If a field contains one of the keywords, return this keyword ?
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?
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?
-
- Posts: 1019
- Joined: Sun May 04, 2014 4:01 pm
- Location: Sydney, Australia
Re: If a field contains one of the keywords, return this keyword ?
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.
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training
Power BI Training
Re: If a field contains one of the keywords, return this keyword ?
I will try to see what I can do.
Thank you for your help!
Thank you for your help!
Who is online
Users browsing this forum: No registered users and 1 guest