cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
aaarzoo Frequent Visitor
Frequent Visitor

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 -

CategoryKeyword
FruitsApple
FruitsBanana
FruitsPear
BreadBread
Breadsandwich
Breadtoast
Meatsalami
Meatpork
MeatBeef
Meatpepperoni

 

Table 2 - 

S.No.Text
1I eat Banana for breakfast
2I eat beef jerky for breakfast
3I eat pear in breakfast
4I had milk and bread
5I 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.TextCategory
1I eat Banana for breakfastFruit
2I eat beef jerky for breakfastMeat
3I eat pear in breakfastFruit
4I had milk and breadBread
5I had banana milk shakeFruit
6I 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!

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: Adding category data to the existing table

@aaarzoo,

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])

1.JPG


Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
Super User III
Super User III

Re: Adding category data to the existing table

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:

https://blog.crossjoin.co.uk/2014/06/25/using-list-generate-to-make-multiple-replacements-of-words-i...

 

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

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!
aaarzoo Frequent Visitor
Frequent Visitor

Re: Adding category data to the existing table

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!

Microsoft jeroenterheerdt
Microsoft

Re: Adding category data to the existing table

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).

aaarzoo Frequent Visitor
Frequent Visitor

Re: Adding category data to the existing table

Thanks... in that case can you suggest how I can resolve this?

Moderator v-yuezhe-msft
Moderator

Re: Adding category data to the existing table

@aaarzoo,

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])

1.JPG


Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

aaarzoo Frequent Visitor
Frequent Visitor

Re: Adding category data to the existing table

Thank you Lydia! It worked like a charm!!

aaarzoo Frequent Visitor
Frequent Visitor

Re: Adding category data to the existing table

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!

 

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors