Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi
I am running a list of keywords against text and getting keywords returned in a column if they appear in the text. The below formula is working fine except I'm also getting words returned even when they are part of another word/string. For example if the text contains the keyword 'got', I'm also getting a return on 'forgot' when I only want a return on 'got'. Any suggestions on what to add to the below formula would be appreciated!
Keyword return = FIRSTNONBLANK(FILTER(VALUES('Reputation Keywords'[Keyword]),
SEARCH('Reputation Keywords'[Keyword],Messages[Message],
1,
0
)
),1)
Thanks
Hi @Ben_Nash,
I have made a test with your formula, it seems that it works as expected.
More details you could have a reference of the attachment.
If you still need help, please share some data sample which could reproduce your scenario and your desired output, so that we can help further investigate on it?
Best regards,
Cherry
Hi v-piga-msft
I've provided a data sample to give you an example of the issue I'm trying to work through.
Whenver a word in the 'keywords' column appears in the 'messages' column, I want it returned in the 'Keyword return' column. This works fine except I only want distinct words returned. Note that the keyword 'sink' gets returned for 'Helsinki' but I don't want this returned. Any suggestions would be greatly appreciated.
Thanks
Ben
@Ben_Nash wrote:Hi v-piga-msft
I've provided a data sample to give you an example of the issue I'm trying to work through.
Whenver a word in the 'keywords' column appears in the 'messages' column, I want it returned in the 'Keyword return' column. This works fine except I only want distinct words returned. Note that the keyword 'sink' gets returned for 'Helsinki' but I don't want this returned. Any suggestions would be greatly appreciated.
Thanks
Ben
Hi Ben,
Here is the text manipulation I did for you.
Cheers,
Swati
ExactWordMatch = VAR startofstring = SEARCH ( Tweets [Keyword return], Tweets[Messages], 1, 0) VAR stringineed = Tweets [Keyword return] VAR Istword = IF(Tweets [Keyword return]<>"" && Startofstring=1 ,Trim(Tweets [Keyword return]),"Dummy") VAR lastword = TRIM(RIGHT(SUBSTITUTE(Tweets[Messages]," ",REPT(" ",LEN(Tweets[Messages]))),LEN(Tweets[Messages]))) VAR spaceinthefront = CONCATENATE(" ",Stringineed) VAR spaceattheend = CONCATENATE(Stringineed, " ") VAR spaceatbothends = CONCATENATE(" ",CONCATENATE(Trim(stringIneed)," ")) VAR checkstring = IF(stringineed = Istword, spaceattheend, IF( stringineed =lastword, spaceinthefront, spaceatbothends)) VAR stringIneed2 = IF(SEARCH((Checkstring), (Tweets[Messages]), ,BLANK()),checkstring, "") RETURN IF ( ISBLANK(stringIneed2),"" , TRIM(stringIneed2) )
Hi Cherry
Thanks for your reply and testing.
An example of the problem I’m facing is with a keyword such as ‘taint’. I’m getting a return on the following text ‘If you like long lines, hidden fees, militaint staff, and all around terrible customer experiences’ whereas the formula should be ignoring this text and only returning on 'taint'
Thanks
Ben
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |