Organizing survey data into one pivot

[allodiba]

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.

[JeteMc]

This proposal employs Power Query and a pivot table.
1. Convert the data on Sheet1 into an Excel table
2. Use From Table/Range on the Data tab to load the Excel table into Power Query
3. Paste the following code into the Advanced Editor:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Score", Int64.Type}, {"Age", type number}, {"Marital Status", type text}, {"Retirement Timeline", type text}, {"Q1. I feel I am in good health currently.", type text}, {"Q2. I am currently getting adequate exercise.", type text}, {"Q3. I am currently eating a healthy diet.", type text}, {"Q4. I am currently getting adequate sleep.", type text}, {"Q5. I take care of my mental health.", type text}, {"Q6. I plan to prioritize my physical and mental health (including activities that counter brain aging) in retirement.", type text}, {"Q7. I need more support with my fitness goals.", type text}, {"Q8. I need more support with my nutrition goals.", type text}, {"Q9. I need more support with my mental health.", type text}, {"Q10. I have an aging spouse/partner or parents that I currently care for/or will care for in retirement.", type text}, {"Q11. I have a pre-retirement tax conscious financial plan that is in good shape.", type text}, {"Q12. I have taken a complete inventory of my finances, track them carefully and know my cash flow and net worth.", type text}, {"Q13. I know the date when I can retire/achieve financial freedom.", type text}, {"Q14. I have debt under control.", type text}, {"Q15. I have an emergency fund.", type text}, {"Q16. I understand and have calculated the impact of longevity risk on my retirement.", type text}, {"Q17. My spouse/partner and I have communicated and are on the same page with our financial plans for retirement.", type text}, {"Q18. I have projected my retirement expenses, income sources and gaps in retirement.", type text}, {"Q19. I have a plan for how I will cover my income gaps in retirement.", type text}, {"Q20. I have a plan for how I will cover my income gaps in retirement.", type text}, {"Q21. I'm worried about running out of money in retirement.", type text}, {"Q22. I have a retirement income distribution strategy that includes a safe withdrawal rate (including what I plan to do with my 401k/IRA(s) at retirement).", type text}, {"Q23. I am confident in my short term and long-term investment decisions (and I am not making knee-jerk reactions in market downturns).", type text}, {"Q24. I have a plan to balance growth and protection in my portfolio (right ratio to protect against short term and long-term volatility, sequence of return risk and inflation).", type text}, {"Q25. I have a Social Security timing strategy to maximize my benefit. Experts estimate that many people leave over $100,000 on the table by choosing the wrong timing strategy.", type text}, {"Q26. I have a tax mitigation plan for retirement (i.e., minimizing taxes as low as possible) and have taxable, tax free and tax deferred buckets set up.", type text}, {"Q27. I have considered how my home equity could be used as an income buffer in retirement.", type text}, {"Q28. I have a healthcare plan and have calculated my healthcare expenses in retirement.", type text}, {"Q29. I have investigated a Health Savings Account as a way to invest for healthcare expenses in retirement.", type text}, {"Q30. I understand my Medicare options and decisions.", type text}, {"Q31. I have a plan to protect against a long-term care incident.", type text}, {"Q32. I have a sound life insurance strategy that gives me a tax-free income stream and protects my heirs.", type text}, {"Q33. I have an estate plan (recent will and trust), and plan for orderly and tax efficient legacy plan for my heirs.", type text}, {"Q34. I have communicated my financial/legal and end of life plans with family/friends.", type text}, {"Q35. I could use the help of a RetireMentor or other specialist for one of the financial/tax/legal readiness areas above.", type text}, {"Q36. I am looking forward to retirement.", type text}, {"Q37. I am worried about my transition to retirement and how I'll handle all the changes it represents.", type text}, {"Q38. I have a lifestyle plan for retirement and/or I've discussed with my spouse/partner.", type text}, {"Q39. I believe I'll be able to live the retirement lifestyle I desire.", type text}, {"Q40. I have strong connections/relationships with family and friends, and look forward to more in retirement.", type text}, {"Q41. I am or plan to go through significant change (i.e. divorce, marriage, career, etc.) prior to or in retirement.", type text}, {"Q42. I have activities and hobbies planned for retirement.", type text}, {"Q43. I have a plan for where I'll live and type of dwelling I'll live in.", type text}, {"Q44. I need some support with my happiness readiness (aka life coaching) as I prepare for retirement.", type text}, {"Q45. After taking this quiz, I feel pretty good about my overall retirement readiness.", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Score", "Age", "Marital Status", "Retirement Timeline"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Question"}, {"Value", "Response"}})
in
    #"Renamed Columns"

5. Close and Load to a new sheet
6. Produce a pivot table from the Power Query table
Let us know if you have any questions.