Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Solved! Go to 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"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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
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"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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
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
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?
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
26 | |
12 | |
11 | |
11 | |
8 |
User | Count |
---|---|
53 | |
29 | |
16 | |
14 | |
13 |