Organizing survey data into one pivo

allodiba
Copper Contributor
Feb 13, 2024
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.
- Lorenzo
  Silver Contributor
  Feb 14, 2024
  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...)
  - allodiba
    Copper Contributor
    Feb 18, 2024
    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.
    PQPP_Financial.xlsx490 KB
allodiba
Copper Contributor
Feb 02, 2024
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!
- Lorenzo
  Silver Contributor
  Feb 02, 2024
  allodiba
  
  #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
  - allodiba
    Copper Contributor
    Feb 02, 2024
    Right before you responded I realized you had just renamed the field, makes sense!
    
    I think that covers what I needed. Thank you again!!

Forum Discussion