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