Forum Discussion

jerryr's avatar
jerryr
New Member
2 years ago
Solved

Assign a category based upon a text search in another column

Hi - I am looking to do the following (example):

 

Table 1

IDFlavor
1Vanilla
2Chocolate
3Rocky Road
4Cookies and Cream

 

 

Table 2

Feedback IDFeedback Text
1001Vanilla is the best
1002I like Cholocate ice cream
1003my favorite is Cookies and Cream
1004We enjoy eating vanilla ice cream during the summer
1005I like ice cream



Table 2 would search Table 1 based upon the Feedback Text (in Table 2) to see if it finds the word in Table 1 (Flavor).

 

The end result would be something like this:

 

Table 2 (new column - flavor)

Feedback IDFeedback TextFlavor (NEW COLUMN)
1001Vanilla is the bestVanilla
1002I like Cholocate ice creamChocolate
1003my favorite is Cookies and CreamCookies and Cream
1004We enjoy eating vanilla ice cream during the summerVanilla
1005I like ice creamUnknown

 

I am looking to do something in Power Query as the data loads from the two tables.

After the dataloads, table 1 is no long needed.

 

Any thoughts ?

 

Thanks - Jerry

 

Show and Tell
  • AlienSx's avatar
    AlienSx
    2 years ago

    jerryr Cholocate <> Chocolate. Other than that 

        Table.AddColumn(
        Table2,
        "Flavor", 
        (w) => 
            try Table1[Flavor]
                {List.PositionOf(
                    Table1[Flavor], 
                    w[Feedback Text], 
                    Occurrence.First, 
                    (x, y) => Text.Contains(y, x, Comparer.OrdinalIgnoreCase)
                )}
            otherwise "Unknown"
    )
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi,

    Thanks for the solution AlienSx  provided, and i want to offer some more information for user to refer to.

    hello jerryr , you can add a custom column.

    let a=List.Transform(#"Table 1"[Flavor],each Text.Lower(_)),
b=Text.Lower([Feedback Text])
in try Text.Proper( List.Select(a,each Text.Contains(b,_)){0}) otherwise "Unknown"

    Output

    And you can refer to the attachment.

     

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • AlienSx's avatar
    AlienSx
    Icon for Super User rankSuper User
    2 years ago

    jerryr Cholocate <> Chocolate. Other than that 

        Table.AddColumn(
        Table2,
        "Flavor", 
        (w) => 
            try Table1[Flavor]
                {List.PositionOf(
                    Table1[Flavor], 
                    w[Feedback Text], 
                    Occurrence.First, 
                    (x, y) => Text.Contains(y, x, Comparer.OrdinalIgnoreCase)
                )}
            otherwise "Unknown"
    )
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi,

    Thanks for the solution AlienSx  provided, and i want to offer some more information for user to refer to.

    hello jerryr , you can add a custom column.

    let a=List.Transform(#"Table 1"[Flavor],each Text.Lower(_)),
b=Text.Lower([Feedback Text])
in try Text.Proper( List.Select(a,each Text.Contains(b,_)){0}) otherwise "Unknown"

    Output

    And you can refer to the attachment.

     

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.