Finding multiple strings to create a conditional column

[ChrisCrosser]

Hi guys,

I have the following dataset.

Author         Message
Chris            I like apples and pears in my cake
Ryan            I hate pears and peaches 

What I want to do, is create a new column where the fruits from [message] are categorized and grouped, but also group snacks together.

Currently I have this:
if
List.AnyTrue(List.Transform({"apple", "pear", "peach"}, (substring) => Text.Contains([Message], substring)))
then
"Fruit"
else if
List.AnyTrue(List.Transform({"cookies, "cake"}, (substring) => Text.Contains([Full Text], substring)))
then
"Snack" else "nothing"

The problem here is, that it only categorizes the first instance: 
Author         Message                                             Category
Chris            I like apples and pears in my cake      Fruit
Ryan            I hate pears and peaches                    Fruit

What I need is:
Author         Message                                             Category
Chris            I like apples and pears in my cake      Fruit, Snack
Ryan            I hate pears and peaches                    Fruit

OR:
Author         Message                                             Category
Chris            I like apples and pears in my cake      Fruit
Chris            I like apples and pears in my cake      Snack
Ryan            I hate pears and peaches                    Fruit

This way, I can count how many posts there are for specific categories (per time window etc.)

Can anyone help me?

Kind regards,

Chris 

[CNENFRNL · Accepted solution]

let
    Lookup = #table({"Category", "Item"}, {{"Fruit", {"apple", "pear", "peach"}}, {"Snack", {"cookies", "cake"}}, {"Cola", {"Coka", "Pepsi"}}}),
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc0xDsIwDAXQq3x5zg0YO7EgxBplMI1FrJQ4irv0NpyFkxVVXdif9GKkqQz174cCXbFoFXDvizi4ZXTh4dCG94aZq1AKkR4bt0MXXuUkJ56L+GFu9rS8/dRklWEDd+muAU10LTKgjpdZvuDvpJR2", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Author = _t, Message = _t]),
    #"Added Custom" = let lookup = Table.ToRecords(Lookup) in Table.AddColumn(Source, "Category", each List.Accumulate(lookup, {}, (s,c) => s & {if List.AnyTrue(List.Transform(c[Item], (substring) => Text.Contains([Message], substring))) then c[Category] else null})),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Category", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
    #"Extracted Values"

[CNENFRNL · Accepted solution]

let
    Lookup = #table({"Category", "Item"}, {{"Fruit", {"apple", "pear", "peach"}}, {"Snack", {"cookies", "cake"}}, {"Cola", {"Coka", "Pepsi"}}}),
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc0xDsIwDAXQq3x5zg0YO7EgxBplMI1FrJQ4irv0NpyFkxVVXdif9GKkqQz174cCXbFoFXDvizi4ZXTh4dCG94aZq1AKkR4bt0MXXuUkJ56L+GFu9rS8/dRklWEDd+muAU10LTKgjpdZvuDvpJR2", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Author = _t, Message = _t]),
    #"Added Custom" = let lookup = Table.ToRecords(Lookup) in Table.AddColumn(Source, "Category", each List.Accumulate(lookup, {}, (s,c) => s & {if List.AnyTrue(List.Transform(c[Item], (substring) => Text.Contains([Message], substring))) then c[Category] else null})),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Category", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
    #"Extracted Values"

[AlvaroPerezJ]

Hi,

Saw you had an amazing reply for a similar problem, If you have the time I would like to ask you a question please.

Description                                                                     New Column

Almonds SMALL and MEDIUM                                      Small, Medium

Almonds SMALL MEDIUM CHIPPED BROKEN LARGE    Small, Medium, Chipped, Broken, Large

That is basically it, how do I do that and have the New Column arrange the output alphabetically?

Thank you so much!

Best

Alvaro

[ChrisCrosser · Accepted solution]

Awesome, this works! Thank you very much.

Minor thing to add: How do I set it up so that it won't be Fruit, Snack on the same row, but create an extra (duplicate) row for every individual category?
Like this:

Author         Message                                             Category
Chris            I like apples and pears in my cake      Fruit
Chris            I like apples and pears in my cake      Snack
Ryan            I hate pears and peaches                    Fruit

[Jakinta]

Here is one of many ways to do it.

= Table.AddColumn( PriorStepName, "Category", each let f=List.AnyTrue(List.Transform({"apple", "pear", "peach"}, (substring) => Text.Contains([Message], substring))), s=List.AnyTrue(List.Transform({"cookies", "cake"}, (substring) => Text.Contains([Message], substring))) in if f and s then "Fruit, Snack"  else if f>s then "Fruit" else if f<s then "Snack" else "")

[ChrisCrosser]

Thanks for the quick reply!

This will work, but it is hard coded. The fruit and snacks categories were just examples to illustrate the problem. In reality I have 10+ categories I want to fit under one column, which means that I will have to put in every possible scenario (If s and f and x and y and z then "Fruit, "Snack", "Burger", "Pizza", "Pancake") manually. Also, more stuff might be added to the categories later.

Is there a less labour intensive way to do this?