Forum Discussion
Text Categorization based on multiple Keywords
Hello @k_mathana
You can try Approximate Merge in the Power Query Editor to add a Category column in Table1.
First, we combine the column (Keyword1 and Keyword2) into Table2. Select two columns, right-click, and select the join column.
Then combine approximately Table1 and Table2 by description and merged column. Click with the fuzzy merge option. Set Similar threshold to 0.3 and Maximum number of matches, 1 to Approximate match options.
Expand category in table2
Result:
You can download the pbix file from this link: Categorizing text based on multiple keywords
Best regards
Rico Zhou
If this post helps,then consider Accepting it as the solution to help other members find it faster.
Hi k_mathana
You can try to build a calculated column by dax. However I think there may be something wrong in the screenshot Table2 you shared with us. In table1 we see Clean the Filter(keyword1=clean , keyword2 = filter), category = Filter Cleaning, but in Table2 if keyword1= clean , keyword2 = filter we can see Category = Filter Replacement. So I update Table 2.
Firstly, we need to transform Table2. Duplicate keyword1 and keyword2 and merge columns (keyword1 * keyword2 , again keyword2.1 * keyword1.1)
Result:
Then build a calculated column as below.
Category = VAR _Description = 'Table 1 (2)'[Description] VAR _Result1 = MAXX ( 'Table 2 (2)', VAR _Keyword1 = 'Table 2 (2)'[M.Keyword1] VAR _Category = 'Table 2 (2)'[Category] VAR _Search = SEARCH ( _Keyword1, _Description, 1, 0 ) RETURN IF ( _Search <> 0, _Category) ) VAR _Result2 = MAXX ( 'Table 2 (2)', VAR _Keyword2 = 'Table 2 (2)'[M.Keyword2] VAR _Category = 'Table 2 (2)'[Category] VAR _Search = SEARCH ( _Keyword2, _Description, 1, 0 ) RETURN IF ( _Search <> 0, _Category) ) RETURN IF(ISBLANK(_Result1),_Result2,_Result1)Result is as below.
You can download the pbix file from this link: Multi-keyword-based text categorization
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Try the following formula. Add a new column
Column =
SWITCH( TRUE(),
SEARCH( "filter", Table[Description],,0) <> 0 , "Filter",
SEARCH( "oil", Table[Description],,0) <> 0 , "Oil",
BLANK()
)
Hi, Thanks for the reply. but Its not only about the lowercase and Upppercase. In table 2 I can maintain the keywords based on that In table 1 it should categorize as per the table 2 keywords.
Hello @k_mathana
You can try Approximate Merge in the Power Query Editor to add a Category column in Table1.
First, we combine the column (Keyword1 and Keyword2) into Table2. Select two columns, right-click, and select the join column.
Then combine approximately Table1 and Table2 by description and merged column. Click with the fuzzy merge option. Set Similar threshold to 0.3 and Maximum number of matches, 1 to Approximate match options.
Expand category in table2
Result:
You can download the pbix file from this link: Categorizing text based on multiple keywords
Best regards
Rico Zhou
If this post helps,then consider Accepting it as the solution to help other members find it faster.
@RicoZhou Thank you, I have tried this before, few Issues I have faced in this solution. Any way thank you so much for your kind response.
Hi k_mathana
You can try to build a calculated column by dax. However I think there may be something wrong in the screenshot Table2 you shared with us. In table1 we see Clean the Filter(keyword1=clean , keyword2 = filter), category = Filter Cleaning, but in Table2 if keyword1= clean , keyword2 = filter we can see Category = Filter Replacement. So I update Table 2.
Firstly, we need to transform Table2. Duplicate keyword1 and keyword2 and merge columns (keyword1 * keyword2 , again keyword2.1 * keyword1.1)
Result:
Then build a calculated column as below.
Category = VAR _Description = 'Table 1 (2)'[Description] VAR _Result1 = MAXX ( 'Table 2 (2)', VAR _Keyword1 = 'Table 2 (2)'[M.Keyword1] VAR _Category = 'Table 2 (2)'[Category] VAR _Search = SEARCH ( _Keyword1, _Description, 1, 0 ) RETURN IF ( _Search <> 0, _Category) ) VAR _Result2 = MAXX ( 'Table 2 (2)', VAR _Keyword2 = 'Table 2 (2)'[M.Keyword2] VAR _Category = 'Table 2 (2)'[Category] VAR _Search = SEARCH ( _Keyword2, _Description, 1, 0 ) RETURN IF ( _Search <> 0, _Category) ) RETURN IF(ISBLANK(_Result1),_Result2,_Result1)Result is as below.
You can download the pbix file from this link: Multi-keyword-based text categorization
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.