Forum Discussion
Solved
Organizing survey data into one pivo
Hi all,
I have attached a spreadsheet of data containing survey questions. The survey asks respondents a number of questions to gauge their retirement readiness and confidence in health and finance. There are preliminary questions for age, marital status, and retirement timeline. The end goal is to organize these responses in a way that allows me to break down the responses based on the prelim questions (ex: divorced people 5 years out from retiring are confident with x but not confident with y).
I have included a screenshot of an early version of this in google sheets, in which I have a pivot table for each question. The issue is that there are 45+ questions, so this is totally inefficient. Is there a way to include all question responses in one pivot that also allows me to break things down based on the prelim questions? I tried using power query and power pivot, but at this point I'm wondering if it's easier or makes more sense to just use filters for the prelim questions.
Any help is appreciated, happy to clarify anything.
Hi allodiba
My PC died a few days ago and the new one hasn't landed yet + time to set it up will be required... So, top of my head (I can't check):
In Power Query:
- Unpivot the Question columns
- Add a Custom column ([Q.ID]) with formula to "extract" the number between "Q" and "." in the newly created (by unpivoting) [Question] column
In Power Pivot:
Sort the [Question] column by the [Q.ID] column (Sorting A Column Of PowerPivot Data By Another Column😞 Select column [Question] > Sort by Column (in the toolbar) > in the By drop-down list select [Q.ID]
(not sure I understand why you opted for a COUNTIF approach on your other thread...)
This is wonderful!
A couple questions:
1. I decided I will separate the prelim questions into pivot tables on separate tabs. i.e. one for marital status, retirement timeline, age bracket. When I went to create a pivot table from power pivot for another prelim question I noticed in the field list I did not have the option for fxResponses under Table 1 to add to the Values box. There's now an option showing fxAnswer% Could you explain why? Screenshots attached.
2. This analysis is something that gets looked at every 6 months or so, when more respondents have taken the assessment. If I wanted to update the raw data, would I just paste that into the Data tab and refresh the pivot tables?
Thank you so much!#1 IF I understand your point that doesn't matter. Actually the Power Pivot Measure is named Answer%:
When you create a new PivotTable from the Data Model you therefore get the above Measure names
Put the Answer% measure in the Values area of your PivotTable. The new column will be auto. named Answer%. Click on the column header and rename it as you want, i.e. Responses
#2 YES, add your new records at the bottom* of the Table in the Data tab > Refresh your PivotTables
* 1st available row after the bottom of the existing Table
.png){kind=link}
.png){kind=link}
.png){kind=link}
