Forum Discussion
Organizing survey data into one pivo
Hi allodiba
Not clear to me what other calculations you expect so here is a starter only:
Done with Power Query & Power Pivot (to allow sorting the Questions in the PivotTable)
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:
- https://support.microsoft.com/en-us/office/unpivot-columns-power-query-0f7bad4b-9ea1-49c1-9d95-f588221c7098 the Question columns
- https://support.microsoft.com/en-us/office/add-a-custom-column-power-query-2dbb579a-915b-4ebd-b622-8e7f3d1d61a6 ([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 (https://excelguru.ca/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...)
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.
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
- Right before you responded I realized you had just renamed the field, makes sense!
I think that covers what I needed. Thank you again!!
.png){kind=link}
.png){kind=link}
.png){kind=link}
.png){kind=link}
