Delimited data - Manipulating data from calendar connection

[carlG]

Hi PowerBi Community,

I am attempting to count the number of times a staff member is listed as a "required attendee" on a shared calendar. So far been able to connect everything, except hitting an issue when more than one person has been marked as required.

The imported data comes in with multiple names separated by a semicolon. I have attempted various ways to separate out this data, but I can't figure out how to build it with dynamic changes to accommodate if more people are added to the calendar invite.

Sample Data:

Subject	Start	End	DisplayTo	Event Type
Class1-Arduino	26/04/2021	26/04/2021	John Smith; Jane Doe	Program
Class2 - RaspPi	27/04/2021	27/04/2021	Jane Doe;	Program
Class3 - Arduino	28/04/2021	28/04/2021	Jane Doe; John Smith	Program
Event - Laser Cutter	28/04/2021	28/04/2021	Steve Jobs; John Smith; Jane Doe	Networking Event
Class4 - Illustrator	29/04/2021	30/04/2021	Jane Doe; Steve Jobs	Program

Expected Result:

Jane Doe: 5

John Smith: 3

Steve Jobs: 2

 I have also tried separating out the delimited data into new rows, but have found that the other data becomes incorrect when I am counting the number of events, etc. 

Any pointers in the right direction or things to try would be greatly appreciated 🙂

[amitchandak · Accepted solution]

@carlG , I think the best is that you have a table where you split the name data by rows.  You can keep a copy of a table like this and have another one where names are splitter into the rows https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/

[carlG]

Thank you @amitchandak,

Was hoping to avoid the second table, but after further testing, and your comment looks like the most feasible way.