Preparing survey data to aggregate responses based on criteria

[Anonymous]

I have 360 survey data where there was a set of 14 questions (12 multi choice and 2 free text). There is about 50 different people who the survey was conducted about - "subjects of the survey" and for each person up to 7 people "respondents" answered the 14 questions about that person. This means there are multiple different answers for each question, per "subject". 

I want to set up my PowerBI report so that it tells me for each "subject" of the survey and for each question, what the most common response was. I need this to be usable in visuals so that for each question, there is only one response for each person (the most common). 

How can I do this? I have unpivoted my data in power query so have a question and answer column. 

[Anonymous]

Thanks for the response and guidance on posting. 

Below is a simplified example of how the data is exported: So you can see for one subject, there is multiple responses to the each question

Responder	Responder Relationship to Subject	Subject	Subject Dept	How would you describe their leadership style?	How you describe their role in a team?	What is their top skill?	What is their top development area?
John	Co-worker	Amy	IT	Coaching	Brings people together	Problem solver	Recognition of peers
Ben	Co-worker	Amy	IT	Affiliative	Key player	Business accumen	Approachability
Sally	Manager	Amy	IT	Pacesetter	Key player	Problem solver	Communication
Amy	Self	Amy	IT	Pacesetter	Key player	Problem solver	Communication
Sarah	Co-worker	Amy	IT	Pacesetter	Brings people together	Inclusvity	Communication
John	Manager	Sarah	IT	Coaching	Key player	Communication	Communication
Ben	Co-worker	Sarah	IT	Commanding	Works in isolation	Decision making	Sharing information
Sally	Co-worker	Sarah	IT	Commanding	Works in isolation	Decision making	Availability
Amy	Co-worker	Sarah	IT	Commanding	Key player	Decision making	Strategy
Sarah	Self	Sarah	IT	Pacesetter	Key player	Communication	Strategy

My expected output is below (as these values were the most common response for that person) 

Subject	Subject dept	How would you describe their leadership style?	How you describe their role in a team?	What is their top skill?	What is their top development area?
Amy	IT	Pacesetter	Key player	Problem Solver	Communication
Sarah	IT	Commanding	Key player	Decision making	Strategy

So then I can create visuals at the department level and have only one response for each person per question, instead of 5 in the original data. 

Hopefully that explains it better 🙂 

[Anonymous · Accepted solution]

hanks for the reply from Greg_Deckler , please allow me to provide another insight:

Hi, @Anonymous 
Thanks for reaching out to the Microsoft fabric community forum.

I suggest you use the List.Mode() function in Power Query, combined with pivot and unpivot operations, to achieve your needs:

1.First, delete unnecessary columns such as Responder and Responder Relationship to Subject, and select all columns except the Subject column for unpivoting.

2.Next, select the "Subject" and "Attribute" columns for a group by operation, and choose the max aggregation method for easier modification.

3.Modify the M code for this step to better meet your needs.

= Table.Group(#"Unpivoted Columns", {"Subject", "Attribute"}, {{"Count", each List.Mode([Value]), type text}})

4.Then, select the "Attribute" column for pivoting, and choose the Count column as the value column. Remember to select "Do Not Aggregate."

5.For more details, please refer to the relevant documentation. I hope these steps help you achieve your goal.

List.Mode - PowerQuery M | Microsoft Learn

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

This is great and exactly what I need - thank you so much!!

A quick question, what happens if someone has multiple 'most common responses', for example if 3 people say Amy is a "Key Player", 3 people say she "Brings People Together" and 1 person says she "Works in isolation" - how does it decide the most common between Key Player and Brings People Together? 

[Anonymous]

Hi, @Anonymous 
Thank you for your prompt response and for accepting my idea as the solution. Regarding your question, there is a corresponding explanation in the official documentation:

For more details, please refer to: 

List.Mode - PowerQuery M | Microsoft Learn

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@Anonymous Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.