Forum Discussion
Organizing survey data into one pivo
.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
AH! That makes sense. I ended up adding Qx. before each question which then showed up correct in power query, but I will edit the question names themselves at the source so next time the data gets exported it reads them with Qx. prefix.
One additional question, might you know why my pivot table is now sorting the questions 1, 10, 11, etc? Instead of in their proper order like it is in the first sheet. It doesn't really matter ultimately, since I'm sorting them with an INDEX(MATCH) function, but would be nice to know.
.png){kind=link}