Assign a category based upon a text search in another column

[jerryr]

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

Table 1

ID	Flavor
1	Vanilla
2	Chocolate
3	Rocky Road
4	Cookies and Cream

Table 2

Feedback ID	Feedback Text
1001	Vanilla is the best
1002	I like Cholocate ice cream
1003	my favorite is Cookies and Cream
1004	We enjoy eating vanilla ice cream during the summer
1005	I 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 ID	Feedback Text	Flavor (NEW COLUMN)
1001	Vanilla is the best	Vanilla
1002	I like Cholocate ice cream	Chocolate
1003	my favorite is Cookies and Cream	Cookies and Cream
1004	We enjoy eating vanilla ice cream during the summer	Vanilla
1005	I like ice cream	Unknown

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

[AlienSx · Accepted solution]

@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 · Accepted solution]

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.

[AlienSx · Accepted solution]

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