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 new to PowerBI. I am trying to categorize data in a table based on certain key words found in a text column. These keywords and corresponding category is stored in another lookup table.
Example -
Table 1 -
Category | Keyword |
Fruits | Apple |
Fruits | Banana |
Fruits | Pear |
Bread | Bread |
Bread | sandwich |
Bread | toast |
Meat | salami |
Meat | pork |
Meat | Beef |
Meat | pepperoni |
Table 2 -
S.No. | Text |
1 | I eat Banana for breakfast |
2 | I eat beef jerky for breakfast |
3 | I eat pear in breakfast |
4 | I had milk and bread |
5 | I had banana milk shake |
I want to add another column in Table 2 which will search for keywork in column "Text" based on keyword from table 1 and populate the category value in the new column.
So, the resulting table 2 should look like -
S.No. | Text | Category |
1 | I eat Banana for breakfast | Fruit |
2 | I eat beef jerky for breakfast | Meat |
3 | I eat pear in breakfast | Fruit |
4 | I had milk and bread | Bread |
5 | I had banana milk shake | Fruit |
6 | I had chocolate milk and Bread | Bread |
I toyed with lookupvalue, contains, related text search but I am not able to get the desired output.
Please let me know how to proceed further.
Thanks in advance!
Solved! Go to Solution.
Please create the following columns in Table 2.
Column1 = FIRSTNONBLANK(FILTER(VALUES('Table 1'[Keyword]),SEARCH('Table 1'[Keyword],'Table 2'[Text],1,0)),1)
ExpectedColumn = LOOKUPVALUE('Table 1'[Category],'Table 1'[Keyword],'Table 2'[Column1])
Regards,
Lydia
Hey,
personally I would not try to solve this in DAX, perfomance is one reason and another one be, how to handle text that contains more than one search string.
Chris Webb wrote this great post about replacing searchwords with replacewords using PowerQuery:
I would adopt this to create a new column, that finally contains a list that than can expanded to new rows.
Hope this gets you started
Regards
Tom
Hi Tom, Thank you for your response!
I am also leanig towards your suggestion of not using DAX but a function to do this but then i came across this article -
http://www.dutchdatadude.com/power-bi-pro-tip-lookupvalue-function/#comment-6237
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.
Thanks!
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).
Thanks... in that case can you suggest how I can resolve this?
Please create the following columns in Table 2.
Column1 = FIRSTNONBLANK(FILTER(VALUES('Table 1'[Keyword]),SEARCH('Table 1'[Keyword],'Table 2'[Text],1,0)),1)
ExpectedColumn = LOOKUPVALUE('Table 1'[Category],'Table 1'[Keyword],'Table 2'[Column1])
Regards,
Lydia
Hi Lydia,
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
[Apple, Banana, Pear, Bread, sandwich, toast, salami, pork, Beef, pepperoni]
Now how does filter function know to get Banana from this list as it was used by search function.
Please let me know. Thanks in advance for all your help!
Thank you Lydia! It worked like a charm!!
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |