Get distinct values from data range and count frequency of each value

[Anonymous]

Hi, I am very new to Power Bi and would please like your help with a question!

I have an Outlook calendar export and would like to show how many times each person in the team attended a meeting. The Attendees initially were all on the same column delimited by a semi-colon. I then split them into multiple columns in order for every attendee to be in a separate column (in total 14 columns). 

How can I create a new table in Power Bi that looks up all 14 columns that contain attendees and gets distinct names of the attendees and then counts the attendance of each person?

The calendar looks something like the below, (I have ommitted columns between Subject and Attendees)

Subject	Attendees.1	Attendees.2	Attendees.3
Meeting A	John	Lucy	Matt
Meeting B	George	John	Karen
Meeting C	Karen	Matt	Lucy
Meeting D	Bill	Karen	Lucy

I want the end result to be: 

Attendee	#
John	2
George	1
Karen	3
Bill	1
Lucy	3
Matt	2

Thank you so much for your help!

[jdbuchanan71 · Accepted solution]

@Anonymous 

In PowerQuery you could unpivot the 14 atendee columns then do a groupby on that column.

[amitchandak · Accepted solution]

@Anonymous , on way, is that you unpivot

https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/

another is trying a measure like

measure = 
countX(distinct(union(allselected(Table[Attendees.1]),allselected(Table[Attendees.2]),allselected(Table[Attendees.3]))),[Attendees.1])	

[jdbuchanan71 · Accepted solution]

@Anonymous 

In PowerQuery you could unpivot the 14 atendee columns then do a groupby on that column.