It is talking about similar requirement and it says using lookupvalue in powerpivot, he was able to do it. I tried this solution in powerpivot and also in powerbi but could not do it. I am thinking there is something silly which I am doing and that is why wrote this post.
I was thinking of someone out there has acheived this in more simpler way.
your requirement is different from what I had in the post on my blog. the lookupvalue function can be used where you need a more-than-1-key relationship between tables. I do not see how that would solve your problem (or maybe I am not getting it).
I copied the logic and it worked fine but not able to comprehend how these set of functions did the work 😞
Search gave me a non-zero number if it find a match, values will give me unique rows of all keywords from the table. The point which is confusing me is that how filter function knows which keyword was found in the text column.
I just wanted to understand the overall flow to ensure I am able to write similar querires :).
For example - I will take first row which has text as " I eat Banana for breakfast".
Search function found Banana at location 7 and returns 7.
Values function will get a table with all keywords