Pivot Table Assistance Large Data Set

[AZExcel]

Hello,

I have a Pivot Table challenge that I don't have the skill set for.

The File is a sheet from a Google form Quality Assessment that we need to be able to measure by questions and responses in columns AX-BA.
Further, the ability to measure the count and percentages by the employee per questions and responses ie " Yes", "No"

I created a formula per row that counts the yes and no responses and give the percentages, but I am trying to figure if there is a way to Pivot this using slicers or whatever other options that I don't have the skill set for.. If anyone can assist even by pointing me in a direction I would greatly appreciate it

I have not posted in several years but am reminded how much value this Forum

Thanks

 

[p45cal]

Power Query in the backround which unpivots all the columns apart from the first 7 which in turn feeds 2 pivot tables.

 

[AZExcel]

P45cal

Thank you for your reply.. I was in Hawaii for a few days and got busy.. I will take a look and see what I can learn.. I really appreciate your time

AZExcel

 

[AZExcel]

P45cal,

Love the Logo or Avatar you use.. anyway.. what was the reason Power Query was necessary to address the problem, and is there a way to include all the questions in this data, for example: how did employee A do with the question in column H or Column I, etc?

 

[p45cal]

what was the reason Power Query was necessary to address the problem

Well, it may not be necessary to use Power Query, but
1. it's dead easy to rearrange the data (unpivot) suitable for the kinds of pivot table you wanted to see.
2. I said:

which unpivots all the columns apart from the first 7

which means that if there are differing numbers of questions and different questions it should cope. The first 7 field names should, however, remain the same.

is there a way to include all the questions in this data, for example: how did employee A do with the question in column H or Column I, etc?

Sure, in the attached, at cell AI32, a copy of one of the other pivot tables rearranged to show the kind of thing you're asking. It's filtered on the Specialist Name (two of them), Response (only Yes and No) and for 2 questions (for which there's also a slicer).
It's down to your ingenuity as to what fields you put into the various parts of the pivot, and what slicers (or timeline) you want to add, and what grouping you might want to add, what's more you could add a pivot chart.
I'd encourage you to experiment with the pivots to see what arrangement of data you might want to use.

Note, in the attached, I added a single step to the existing 2 step Power Query query changing the time stamp column to a proper time/date data type, which means you can add that field to the pivot as a proper date/time field and filter on it or group on it or choose what dates/times you want to include in the pivot with a timeline.

 

[AZExcel]

p45cal

Thank you for the reply... I am reviewing your latest submission to learn what I don't know. I really appreciate your assistance. CHEERS!!

AZExcel

 

[AZExcel]

P45cal,

Wanted to thank you for sharing your knowledge. I have been moving which is why there is such a gap in this entry. Having said that, I have been reviewing your submissions going into the Power Query editor, and watching videos on Power Query and now I feel like I am starting to understand the value of Power Query... I now understand what you meant by unpivoting the column, which was a missing piece of my understanding in terms of getting the questions into one column and the responses.. it was not obvious to me how that was done.. Thanks for taking the time to help..