Asssign category based upon text in a field

[jerryr125]

Hi - I am looking to do the following :

(I posted this question before but cannot seem to locate it!)

I would like to assign a category to the master table (Master Table) based upon search criteria in another table (Table 1)

Example

Master Table (input)

ID	Text	Category
1	I like ice cream
2	Apple trees are growing in the yard
3	Here is a bowl of nuts
4	I ate some ice cream on Sunday

Table 1

Search Text	Category
Ice Cream	Dessert
Cake	Dessert
Banana	Fruit
Apple	Fruit
Petzels	Snack
Nuts	Snack

Final - Master Table (output) - search Text field so to speak and determine the category

ID	Text	Category
1	I like ice cream	Dessert
2	Apple trees are growing in the yard	Fruit
3	Here is a bowl of nuts	Snack
4	I ate some ice cream on Sunday	Dessert

Any help would be appreciated - Jerry

[ThxAlot]

Simple enough,

let
    Master = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc2xCsIwFIXhVzlkLoLaF3AQdHFpt5Ahptcm2OSWJKX07b1kcj4f59danVWnnljClxAcwWWyUZlOq4sMt3VdCDUTFdhMmDPvIc0ICdUTDpunZq9iHyQgiMOb9wX8QdpqaXPfGrYSCse/Djhh2NJkj8bG+zCKfDGirc63UKVY4OX6pIz5AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Text = _t]),
    Search = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kxOVXAuSk3MVdJRckktLk4tKlGK1YlWck7MTkUTckrMA0KgoFtRaSZEyLGgICcVRSQgtaQqNacYKBacl5icDRbzKy1BEogFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Search Text" = _t, Category = _t]),
    #"===========" = null,

    Categorized = let cols = Table.ToColumns(Search) in Table.AddColumn(Master, "Category", each try cols{1}{List.PositionOf(cols{0}, [Text], 0, (x,y) => Text.Contains(y, x, Comparer.OrdinalIgnoreCase))} otherwise "", type text)
in
    Categorized

[jgeddes · Accepted solution]

You can use Comparer.OrdinalIgnoreCase if you do not want to deal with the case of the values.

The code below provides an example of this.

    Table.AddColumn(
        #"Changed Type",                            //previous step in the query
        "Category", 
        each 
        let 
            searchString = [Text]                   //set the current row [Text] column into a variable
        in 
            Text.Combine(                           //combines the values of the matching Category column list to a single string with a comma delimiter
                Table.SelectRows(
                    Table2,                         //select rows in Table 2 that have matching text
                    each 
                    Text.Contains(
                        searchString,               //look in the [Text] column of the current row of Table
                        Record.Field(               
                            _,                      //the current row record in Table2
                            "Search Text"           //column name of the values to search for in Table2
                        ), 
                        Comparer.OrdinalIgnoreCase  //ignores the case of the text during the comparison
                    )
                )[Category],                        //converts the Category column from the resulting selected rows from Table2 into a list
                ","
            )
        )

[jerryr125]

Hi - I copied the code above into the advance editor of the table - I get a EOF error.

Here is what the code looks like:

let

  Source = Excel.Workbook(Web.Contents("test.xlsx"), null, true),

  #"Navigation 1" = Source{[Item = "Master", Kind = "Sheet"]}[Data],

  #"Promoted headers" = Table.PromoteHeaders(#"Navigation 1", [PromoteAllScalars = true]),

  #"Transform columns" = Table.TransformColumnTypes(#"Promoted headers", {{"ID", type text}, {"Text", type text}}),

  #"Replace errors" = Table.ReplaceErrorValues(#"Transform columns", {{"ID", null}, {"Text", null}})

in

  #"Replace errors"

  Table.AddColumn(

        #"Changed Type",                            //previous step in the query

        "Category",

        each

        let

            searchString = [Text]                   //set the current row [Text] column into a variable

        in

            Text.Combine(                           //combines the values of the matching Category column list to a single string with a comma delimiter

                Table.SelectRows(

                    Table1,                         //select rows in Table 1 that have matching text

                    each

                    Text.Contains(

                        searchString,               //look in the [Text] column of the current row of Table

                        Record.Field(              

                            _,                      //the current row record in Table1

                            "Search Text"           //column name of the values to search for in Table2

                        ),

                        Comparer.OrdinalIgnoreCase  //ignores the case of the text during the comparison

                    )

                )[Category],                        //converts the Category column from the resulting selected rows from Table1 into a list

                ","

            )

        )

***********************

Tables:

 Master

Table 1

[jerryr125]

Hi- I would like to give this a try - where do I paste the code ? 

[ZhangKun]

Words need to be lowercase：

// Table1
let
    源 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kxOVXAuSk3MVdJRckktLk4tKlGK1YlWck7MTkUTckrMA0KgoFtRaSZEyLGgICcVRSQgtaQqNacYKBacl5icDRbzKy1BEogFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Search Text" = _t, Category = _t]),
    更改的类型 = Table.TransformColumnTypes(源,{{"Search Text", type text}, {"Category", type text}}),
    已添加自定义 = Table.AddColumn(更改的类型, "Lower", each Text.Lower([Search Text]))
in
    已添加自定义

The sentence also needs to be converted to lowercase. If there are multiple results (the last row), they will be concatenated with commas

// Master Table
let
    源 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc47DsIwEIThq4y2TsPrAHSkprRcLPESLJx15DiKcnsWN1D/nzTjHB2oox4pvgVxEAxFeCLfOTpauM5zEtQisoCLYCx5izoiKupLsHMJzZ7M3sRANIdH3hLyE7rWpeWzZfTgKljy9DeErLivGnhv7mLu11gD+HuAvP8A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Text = _t]),
    更改的类型 = Table.TransformColumnTypes(源,{{"ID", Int64.Type}, {"Text", type text}}),
    已添加自定义 = Table.AddColumn(更改的类型, "match", each Text.Combine(Table.SelectRows(Table1, (r) => Text.Contains(Text.Lower([Text]), r[Lower]))[Category], ","))
in
    已添加自定义