Text Categorization based on multiple Keywords

[k_mathana]

Hi

I need to your kind help to categorize the sentence based on two or more keywords, Please refer the below reference . In Table 2 I can maintain the keyword and category.

Table 1

Table 2

[v-rzhou-msft · Accepted solution]

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.

[themistoklis]

@k_mathana 

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

[k_mathana]

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.

[v-rzhou-msft · Accepted solution]

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.

[k_mathana]

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

[v-rzhou-msft · Accepted solution]

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. 

[k_mathana]

Dear @v-rzhou-msft , Thats the exact solution I am looking for. Thank you so much for your time and the fantastic solution. Thats worked great.