Forum Discussion
Adding category data to the existing table
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!
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
7 Replies
- TomMartens
Super User
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!
- jeroenterheerdt
Microsoft Employee
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).
