Jun 08 2021 02:57 AM
Hi
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
Jun 08 2021 05:03 AM
Solution@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?
File attached.
Jun 08 2021 06:19 AM
Jun 08 2021 08:13 AM
@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:
https://exceloffthegrid.com/power-query-introduction/
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.
Jun 10 2021 01:30 AM
@Riny_van_Eekelen Thanks very much for you input Riny, i will try that out
Jun 08 2021 05:03 AM
Solution@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?
File attached.