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

     

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