Splitting Column And Show Containing

[Anonymous]

Hello,
I need help with how to split a column and then create a filter based on the splitted values. 
The column looks like this:

Teams

aaa

aaa, bbb, ccc

bbb, ccc,

bbb

aaa, bbb

And I want to create a slicer so i can filter on either of the teams and I if I filter the activity on another visual, I want to show which teams are participating (i.e. when i filter to the activity on third row, I want to show a multi row card with only bbb and ccc)

Thank you very much for your help!

[mwegener · Accepted solution]

Hi @Anonymous 

you could use Split Column by Delimiter in Power Query and you could create a bridge table with it.

[Anonymous]

@mwegener But then I get duplicates of all values that had more than one team in the table, which ruins my other calculations. Or am I missing something?

[mwegener]

@Anonymous this is a many-to-many relationship, here you have to work with a bridge table. You can find more information in the Microsoft Docs.

https://docs.microsoft.com/en-us/power-bi/guidance/relationships-many-to-many?WT.mc_id=DP-MVP-5004288

[Anonymous]

@mwegener 
I'm sorry but I don't get it. 
I got a new column in my main table with all rows with several teams got duplicated with the amounts of teams that were participating. 
Should I create a new table?
Can you create a table where the relationship is CONTAINSSRING?

[mwegener]

Hi @Anonymous ,

yes you need an additional table that assigns teams to team groups, since this is a many-to-many relationship.
If you provide sample data, I can show it with an example.

[Anonymous]

I managed to do it 🙂
Thank you so much for your help!

[amitchandak]

@Anonymous , The best way, is to split it into rows in power query using split by delimiter

[Anonymous]

@amitchandak But don't I get a lot of columns that way? How do I then add a slicer for all of them? And if I have a lot of rows, isn't it expensive computationally to add columns?