Forum Discussion

jerryr125's avatar
jerryr125
Icon for Helper IV rankHelper IV
1 year ago
Solved

Asssign category based upon text in a field

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)

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

 

 

Table 1

 

Search TextCategory
Ice CreamDessert
CakeDessert
BananaFruit
AppleFruit
PetzelsSnack
NutsSnack

 

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

 

IDTextCategory
1I like ice creamDessert
2Apple trees are growing in the yardFruit
3Here is a bowl of nutsSnack
4I ate some ice cream on SundayDessert

 

Any help would be appreciated - Jerry

Need Help
  • jgeddes's avatar
    jgeddes
    1 year ago

    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
                ","
            )
        )

     

5 Replies

  • jgeddes's avatar
    jgeddes
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
      jerryr125
      Icon for Helper IV rankHelper IV
      1 year ago

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

    • jerryr125's avatar
      jerryr125
      Icon for Helper IV rankHelper IV
      1 year ago

      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

       

  • ZhangKun's avatar
    ZhangKun
    Icon for Super User rankSuper User
    1 year ago

    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
    已添加自定义
  • ThxAlot's avatar
    ThxAlot
    Icon for Super User rankSuper User
    1 year ago

    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