How to count multiple value in one cell and creating new grouping

[Anonymous]

Hi all,

I have looked at other forums and saw that they used count rows with search function to calculate. But is there a way to also group/rename the occurence. 

The data I can extract is like the sample below: (which is behaviour of drivers recorded as per event).

EventID	Behavior
EUGS15420	Braking, Driver Unbelted [Yard]
EUGS21193	Rough/Uneven Surface
EUGS28897	Rough/Uneven Surface, Cell Handheld - Observed
EUGS40268	Accelerating
EUGS47610	Braking, Driver Unbelted [Yard]
EUGS69334	Braking, Driver Unbelted [Yard]
EUGS71831	Other, Possible Collision
EUGS72333	Accelerating, Driver Unbelted [Yard]
EUGS69334	Other, Driver Unbelted [Yard]
EUGS69687	Rough/Uneven Surface, Cell Handheld - Observed, Braking, Driver Unbelted [Roadway]

I want to create a table visual in Power BI that could count each behaviour seperated by a comma, and also create a new group for them: eg, Driver Unbelted should count both [Yard] and [Roadway] 

Sample output:

Braking - 4

Driver Unbelted - 6

Cellphone Observed - 2

Rough Condition - 3

Possible Collision - 1

Other - 2

etc...

Would I need to make another table to categories each behavior first?

Thanks in advance.

[Anonymous · Accepted solution]

I ended up doing this, it might not be the best way as it takes very long time to define all the grouping but it works:

In power query> split column by delimitor > unpivot the new columns > delete the atrribute column and keep the value only>

save and apply

Create new calculated column and use the code below to create my grouping

BehaviorFilter = SWITCH(

    TRUE(),

    SEARCH("Key Word1", 'Table'[Behavior], 1, 0) > 0, "Group Name1",

    SEARCH("Key Word2", 'Table'[Behavior], 1, 0) > 0, "Group Name1",

    SEARCH("Key Word3",'Table'[Behavior], 1, 0) > 0, "Group Name2",
"Other"
)

Hope it helps someone else.