SOLVED

Organizing survey data into one pivo

Copper Contributor

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.

11 Replies
best response confirmed by allodiba (Copper Contributor)
Solution

Hi @allodiba 

 

Not clear to me what other calculations you expect so here is a starter only:

Sample.png

 

Done with Power Query & Power Pivot (to allow sorting the Questions in the PivotTable)

@Lorenzo 

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!

 

@allodiba 

 

#1 IF I understand your point that doesn't matter. Actually the Power Pivot Measure is named Answer%:

 

Sample.png

 

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

Right before you responded I realized you had just renamed the field, makes sense!

I think that covers what I needed. Thank you again!!
You're welcome. At the bottom of each post you get here there's a Mark as answer link. Clicking it helps people who Search for existing solutions - Thanks

@Lorenzo 

Hello again, could you explain how you rearranged the data with power query and power pivot? I have another set of data I want to use for this same purpose, but with a couple new table fields.


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...)

@Lorenzo 

Hi,

When I create a custom column for Question ID, it returns null for each cell. Might you know why? I have screenshot and file attached.


For my other question, I used COUNTIF to find the number of people with scores in specific ranges broken down demographically.

=COUNTIFS(Table1[Score],"<46",Table1[Retirement Timeline], "Already Retired")

Count the number of people who scored less than 42 that are already retired.

Hi @allodiba 

 

When I create a custom column for Question ID, it returns null for each cell. Might you know why?

Does the picture helps you understand?

sample.png

 

==> The data set you use as Source doesn't include a question prefix like Q1.

 

IF, despite the lack of prefix the questions are ordered left to right before you unpivot them:

  • Delete the above step (Added Custom)
  • Add and [Index] column starting at 1
  • Rename the above [Index] as [Q.ID]

Re. the other question: this is the kind of things that are usually doable with Power Pivot measures -  can be tricky though

@Lorenzo 

AH! That makes sense. I ended up adding Qx. before each question which then showed up correct in power query, but I will edit the question names themselves at the source so next time the data gets exported it reads them with Qx. prefix.

 

One additional question, might you know why my pivot table is now sorting the questions 1, 10, 11, etc? Instead of in their proper order like it is in the first sheet. It doesn't really matter ultimately, since I'm sorting them with an INDEX(MATCH) function, but would be nice to know. 

@allodiba 

 

might you know why my pivot table is now sorting the questions 1, 10, 11, etc?

Please refer to my post from Feb 14 2024 09:55 AM. You should get:

sample.png

1 best response

Accepted Solutions
best response confirmed by allodiba (Copper Contributor)
Solution

Hi @allodiba 

 

Not clear to me what other calculations you expect so here is a starter only:

Sample.png

 

Done with Power Query & Power Pivot (to allow sorting the Questions in the PivotTable)

View solution in original post