Forum Discussion
Finding multiple strings to create a conditional column
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
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"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
5 Replies
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 "")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?
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"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 FruitHi,
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
