Adding category data to the existing table

[aaarzoo]

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!

[TomMartens]

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

[aaarzoo]

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]

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]

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

[v-yuezhe-msft · Accepted solution]

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

Regards,
Lydia

[aaarzoo]

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!

[aaarzoo]

Thank you Lydia! It worked like a charm!!