What's the easiest way to group data

[Anonymous]

Hi, how to group "Detail" like below in power BI, it's quite a lot under "detail" may need to add into the query on regular basis if any new ones appears under "detail".   i know the way to add a column may dax=IF(C7="red","color",IF(C7="banana","fruit"))  but that will end very mass as the long list under "detail", is there a easy way to do this?

Detail	Category
red	color
banana	fruit
blue	color
apple	fruit
yellow	color

[BhaveshPatel]

Your requirements are bit confusing. 

Can you please explain what do you actually want to do with your data. What do you mean by grouping.

[Anonymous]

@BhaveshPatel  see my first table, i want to group or categorise "detail" data into group or category, for example, "red" goes to "color", "apple" goes to "fruit".

thanks

[BhaveshPatel]

PowerBI automatically aggragates the data depending on your data type. for text data type, it will count the values and for number data type, it sums up the data.Bar GraphTable Visualization

[Anonymous]

i mean i only got the "detail" data, apple, red, blue, banana...etc.   I would like to group them into "color" "fruit" so i can chart them.

[BhaveshPatel · Accepted solution]

you have to create a two helper tables 

Fruit

Banana

Apple

orange

and

Color

Yellow

Red

Blue

Create a relationship between these tables and you can group and aggregate the data. It doesn't automatically recognize that whether it is color or fruit. you have to create a relationship between bridge tables and your data.

This would solve your problem. let me know if you get stuck.

[Anonymous]

@BhaveshPatelthat's what i'm doing now in excel, but just want to confirm there is no easy way in power bi.   thanks for the help.

[BhaveshPatel]

You can create the custom column in powerquery using the logic shown above and it will solve your problem dynamically.

[BhaveshPatel]

Unfortunately not. 

[BhaveshPatel]

Use Group_By feature of the PowerQuery to solve this problem.

It is Dynamic and would solve your problem of manually updating the formula.

[Anonymous]

@BhaveshPatel thanks, but i can't find "contain" under "operation"? do you mind explain a bit detail?