Hi there. Hope someone can help!
I am trying to build a table to help me prioritize a list of contacts based on a set of keywords
I have one table called KW_Rank which holds a list of keywords, and a score of 1-3:
EG
| Keyword | Rank |
| Good | 3 |
| ok | 2 |
| bad | 1 |
In a second table, Workspace I have contact details, and then a merged column that has a text string for each row
| Text | |
| [email protected] | Fire Good Ok |
| [email protected] | Green Bad |
| [email protected] | Red |
I would like to add a column, which shows a total based on if those words are in the string and the value in score column. If multiples appear i would like these to total up too:
| Text | Score | |
| [email protected] | Fire Good Ok | 5 |
| [email protected] | Green Bad | 1 |
| [email protected] | Red | 0 |
Not sure if I am reaching the end of what i can do in PQ and if i need to read more into power pivot to achieve this or not, but im struggling to find what I need, partly as it is fiddley to explain!
Thanks!
JHZ
Hi JHZ,
Power Pivot can't do this. However, have you tried using fuzzy matching for this? See lesson 3.05 from the Power Query course on Merging Queries.
If you can't do it with that, then you could try using AI to extract the sentiment and categorize the text. See my tutorial on the new LABS.GENERATIVEAI function.
Mynda
