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)
.png){kind=link}
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.
.png){kind=link}
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.
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?
==> 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
.png){kind=link}