Summary of survey scores in one pivot table from PowerPivot

[mikkol]

Hi after reading many entries previously I finally hit a problem that wasn't answered previously

I have some survey data like this:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Number of shops[/TD]
[TD]Price range[/TD]
[TD]Product range[/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD]1[/TD]
[TD]Neutral[/TD]
[TD]Satisfied[/TD]
[TD]Satisfied[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Satisfied[/TD]
[TD]Neutral[/TD]
[TD]Dissatisfied[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Satisfied[/TD]
[TD]Neutral[/TD]
[TD]Neutral[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Neutral[/TD]
[TD]Dissatisfied[/TD]
[TD]Dissatisfied[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Satisfied[/TD]
[TD]Neutral[/TD]
[TD]Satisfied[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Neutral[/TD]
[TD]Satisfied[/TD]
[TD]Dissatisfied[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Neutral[/TD]
[TD]Satisfied[/TD]
[TD]Dissatisfied[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Dissatisfied[/TD]
[TD]Neutral[/TD]
[TD]Neutral[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Neutral[/TD]
[TD]Satisfied[/TD]
[TD]Neutral[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Satisfied[/TD]
[TD]Dissatisfied[/TD]
[TD]Neutral[/TD]
[/TR]
</tbody>[/TABLE]

And I want to create a pivot table like this:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Number of shops[/TD]
[TD]Price range[/TD]
[TD]Product range[/TD]
[/TR]
[TR]
[TD]Dissatisfied[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Neutral[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Satisfied[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

As the measuring fields are placed side-by-side I naturally think that PowerPivot is required to handle this task. So I loaded the data in PowerPivot. But then I realized there needs to be a relationship between the 3 evaluation texts on rows to the columns at top. So I added it. And then you would've figured that multiple relationships between 2 tables aren't allowed, meaning this table doesn't seem to be achievable. Is that correct?

Thanks in advance for you guys' help!

 

[XLBob]

All you need to do is use Power Query to UnPivot your table and go from there.
grab the sample workbook from below(book1 is for u).
https://onedrive.live.com/?cid=7DF5E31C189063EC&id=7DF5E31C189063EC!124

 

[scottsen]

Ya, reshaping your data is going to be very helpful here.

I think you would have to have a disconnected table (table with no relationships) with {Dissatisfied, Neutral, Satisfied } of the satisfaction levels. Then "tie that back in" with a measure. Something like:

NumShops := IF(HASONEVALUE(SlicerTable[SatisfactionName]), CALCULATE(COUNTROWS(DataTable), FILTER(DataTable, DataTable[Number of Shops] = VALUES(SlicerTable[SatisfactionName])))

(Which I totally didn't try out, but sorta makes sense to me )

 

[mikkol]

Wow thanks both esp. XLBob for the example! I would've stuck whole day w/o realizing this was supposed to be pretty straightforward!

I've never heard of Power Query until today and that was like another miracle of using Excel. I gotta dig into that unPivot function that which would save me lots of time instead of transposing those data manually.

The next question comes up: as there are lots of questions + responses I'm expecting close to 6 million rows after this transposition, would Power Query handle huge datasets like PowerPivot does?

Thanks again.