Forum Discussion
Keyword scoring twitter posts
Hi Colourtext ,
There might be mor elegant solution, but here is one alternative:
1) in power query, copy column of twitter post and then split it by delimited (space delimiter)
2) select all new columns except original column and go to transform, choose unpivot columns. In this step, we created row per each word
3) exit Power Query, go to relationships and create join between main table and Keywords Score table; make join between [TwitterPost].Value (new column with distinct words) and [Keywords Score].Keyword
4) in table [TwitterPost] add several columns:
- Keyword value = RELATED(Keywords[Score]) -- to get score for each word
- IsDuplicate = CALCULATE(COUNT(TwitterPost[Value]),ALLEXCEPT(TwitterPost,TwitterPost[MidText],TwitterPost[Value])) -- to check if words is shown multiple times
- Final Score = TwitterPost[Keyword value]/TwitterPost[IsDuplicate] -- final calculation to be displayed (measure)
Cheers,
Nemanja
Hi Nemanja
Thanks so much for looking at this problem. Your solution works well for single word keywords but not with multi-word entities like "brown dog" or "quick fox". I get a random number of multi-word entities in the keyword lists I work with, so sadly I'll have to keep looking.
My best solution for far in Dax works as follows:
OnTopic Keyword Scores =
CONCATENATEX(
//Iterate through keyword table
'My Keywords',
IF(
SEARCH(FIRSTNONBLANK('My Keywords'[Keywords],1),'My Table'[TwitterPost],,999) <> 999,
//Add a delimiter to separate individual keyword scores within the output
CONCATENATE('My Keywords'[Keywords Score],","),
""
)
)Once this calculated column is done I then have to output the table to a .csv via DaxStudio, then re-import this .csv file and split the previously calculated column in Query Editor by the comma delimiters. I have to do this because I can't split a calculatedcoloumn directly within Dax or the Query Editor.
If you can think of any improvements on this process I would be delighted to receive them!
Best
Jason
