Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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

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)

SubjectAttendees.1Attendees.2Attendees.3
Meeting AJohnLucyMatt
Meeting BGeorgeJohnKaren
Meeting CKarenMattLucy
Meeting DBillKarenLucy

 

I want the end result to be: 

Attendee#
John2
George1
Karen3
Bill1
Lucy3
Matt2

 

Thank you so much for your help!

2 ACCEPTED SOLUTIONS
jdbuchanan71
Super User
Super User

@Anonymous 

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

jdbuchanan71_0-1594661236067.png

jdbuchanan71_1-1594661247103.png

 

View solution in original post

amitchandak
Super User
Super User

@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])	

 

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@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
Super User
Super User

@Anonymous 

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

jdbuchanan71_0-1594661236067.png

jdbuchanan71_1-1594661247103.png

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.