Pivot tabel - team, questions with multiply answers

New Contributor


Hope anyone can give me some input.

I hope for input with plain excel functions and features and no VBA please ;o)


I'm doing surveys for my org. and we have several teams who get the same 2 questions.

We get different reply on the questions and we would like to analyse the answers in a pivot.


How do i create a pivot that 

1. Where i can use the filter to select the team

2. Where i get the questions listed below the team with various answers pr. team after the question


I have created a mockup on my desired to be result - see attachment


Thx for your input 

4 Replies
best response confirmed by allyreckerman (Microsoft)

@Jan_Svendsen Perhaps a combination of Power Query (PQ) and Power Pivot (PP) is what you need. Not exactly the same as in your picture but close. Are you familiar with these tools?

Screenshot 2021-06-08 at 14.00.37.png

File attached. 

Hi Riny
Thanks verry much for quick reply.
Is not familiar with PQ and PP but i probally can read up on it.
What is the step's to get the result you attached?

1. Create rawdata
2. nnn
3. nnn

@Jan_Svendsen The PP part is relatively easy. Since I did not have anything to work with, I created a small table with raw data similar to yours and connected to it with PQ. Then changed all data types to text, unpivoted the "Question" columns. close and load (connection only) into the Data Model (DM).


In the DM, I added a measure with a DAX function CONCATENATEX. It basically creates values from texts that can then be dumped into the value field of a pivot table. A trick I learned on this forum a while ago. 


There are many resources on-line to get into the basics of PQ. For instance, this one: 

From here you will be able get to know PQ, connect to your own (real) data and replicate the few applied steps that I used to create the table that loaded into PP.


The DAX part in PP is a bit of a challenge, at least, that's how I see it. I don't even dare start explaining as I would probably talk nonsense. Google for the function, and you'll get plenty of hits.

@Riny_van_Eekelen Thanks very much for you input Riny, i will try that out