Layout help for Pivottable - Yes/No from multiple questions

[trikky]

I have a substantial database with multiple entries for employee evaluations – each entry showing YES/NO/NA responses for up to 40 individual questions.

Currently, I have a summary spreadsheet with various formulas (sumproduct, etc) that presents results based on selected employee name and months, but due to the size of the data, it’s relatively slow when doing calculations. I am thinking a pivottable would enable me to present the data faster on my summary while enabling me to use slicers to drill down a little more.

Here is a sample of my data.

Agent	Topic	Date	ID	Q1	Q2	Q3	Q4
Employee 1	Topic 1	2018-10-18	AB	Yes	No	Yes	Yes
Employee 2	Topic 2	2018-10-12	BC	No	Yes	Yes	N/A
Employee 3	Topic 3	2018-10-01	AD	Yes	Yes	No	No
Employee 1	Topic 1	2018-10-18	BF	Yes	No	N/A	Yes
Employee 2	Topic 2	2018-10-12	QZ	N/A	Yes	Yes	No
Employee 3	Topic 3	2018-10-01	ZY	No	N/A	No	N/A

<tbody>
</tbody>

This is what I’m trying to accomplish (or something similar).

Agent	Q1			Q2
	Yes	No	N/A	Yes	No	N/A
Employee 1	2				2
Employee 2		1	1	2
Employee 3	1	1		1		1

<tbody>
</tbody>

I’m not worried about which labels are on the X axis, and which are on the Y. Additionally, I don’t need all employees listed in the pivottable, as long as the end result is the YES/NO/NA count for each question, for the employees I have selected in the slicers.

My attempts to figure out what goes in the values, columns, and rows fields has wound up to be quite messy and certainly does not result in anything close to my desired output, so I thank you for any assistance you can offer on what data field should go where.

 

[sandy666]

is that what you want?

Agent

Topic

Date

ID

Q1

Q2

Q3

Q4

Count of Value

Attribute

Value

Employee 1

Topic 1

18/10/2018​

AB

Yes

No

Yes

Yes

Q1

Q2

Q3

Q4

Employee 2

Topic 2

12/10/2018​

BC

No

Yes

Yes

N/A

Agent

Yes

No

N/A

Yes

No

N/A

Yes

No

N/A

Yes

No

N/A

Employee 3

Topic 3

01/10/2018​

AD

Yes

Yes

No

No

Employee 1

2​

2​

1​

1​

2​

Employee 1

Topic 1

18/10/2018​

BF

Yes

No

N/A

Yes

Employee 2

1​

1​

2​

2​

1​

1​

Employee 2

Topic 2

12/10/2018​

QZ

N/A

Yes

Yes

No

Employee 3

1​

1​

1​

1​

2​

1​

1​

Employee 3

Topic 3

01/10/2018​

ZY

No

N/A

No

N/A

if yes, use PowerQuery

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Agent", type text}, {"Topic", type text}, {"Date", type datetime}, {"ID", type text}, {"Q1", type text}, {"Q2", type text}, {"Q3", type text}, {"Q4", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Topic", "Date", "ID"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Agent"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"[/SIZE]

then PivotTable from external data source

 

[trikky]

is that what you want?

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Agent[/COLOR]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Topic[/COLOR]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Date[/COLOR]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]ID[/COLOR]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Q1[/COLOR]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Q2[/COLOR]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Q3[/COLOR]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Q4[/COLOR]

Count of Value

Attribute

Value

Employee 1

Topic 1

18/10/2018​

AB

Yes

No

Yes

Yes

Q1

Q2

Q3

Q4

Employee 2

Topic 2

12/10/2018​

BC

No

Yes

Yes

N/A

Agent

Yes

No

N/A

Yes

No

N/A

Yes

No

N/A

Yes

No

N/A

Employee 3

Topic 3

01/10/2018​

AD

Yes

Yes

No

No

Employee 1

2​

2​

1​

1​

2​

Employee 1

Topic 1

18/10/2018​

BF

Yes

No

N/A

Yes

Employee 2

1​

1​

2​

2​

1​

1​

Employee 2

Topic 2

12/10/2018​

QZ

N/A

Yes

Yes

No

Employee 3

1​

1​

1​

1​

2​

1​

1​

Employee 3

Topic 3

01/10/2018​

ZY

No

N/A

No

N/A

<tbody>
</tbody>

if yes, use PowerQuery

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Agent", type text}, {"Topic", type text}, {"Date", type datetime}, {"ID", type text}, {"Q1", type text}, {"Q2", type text}, {"Q3", type text}, {"Q4", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Topic", "Date", "ID"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Agent"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"[/SIZE]

then PivotTable from external data source

Thanks. Now, the only problem here is that I have never used PowerQuery so don't know where to put that. Additionally of course have no idea exactly what the code translates to, so don't know how I can modify it to suit my actual workbook fields.

Just upgraded (at the office) to Office365 so it's Excel 2016.

 

[trikky]

is that what you want?

[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]Agent[/COLOR]

[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]Topic[/COLOR]

[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]Date[/COLOR]

[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]ID[/COLOR]

[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]Q1[/COLOR]

[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]Q2[/COLOR]

[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]Q3[/COLOR]

[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]Q4[/COLOR]

Count of Value

Attribute

Value

Employee 1

Topic 1

18/10/2018​

AB

Yes

No

Yes

Yes

Q1

Q2

Q3

Q4

Employee 2

Topic 2

12/10/2018​

BC

No

Yes

Yes

N/A

Agent

Yes

No

N/A

Yes

No

N/A

Yes

No

N/A

Yes

No

N/A

Employee 3

Topic 3

01/10/2018​

AD

Yes

Yes

No

No

Employee 1

2​

2​

1​

1​

2​

Employee 1

Topic 1

18/10/2018​

BF

Yes

No

N/A

Yes

Employee 2

1​

1​

2​

2​

1​

1​

Employee 2

Topic 2

12/10/2018​

QZ

N/A

Yes

Yes

No

Employee 3

1​

1​

1​

1​

2​

1​

1​

Employee 3

Topic 3

01/10/2018​

ZY

No

N/A

No

N/A

<tbody>
</tbody>

if yes, use PowerQuery

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Agent", type text}, {"Topic", type text}, {"Date", type datetime}, {"ID", type text}, {"Q1", type text}, {"Q2", type text}, {"Q3", type text}, {"Q4", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Topic", "Date", "ID"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Agent"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"[/SIZE]

then PivotTable from external data source

Okay, so I've used thePowerQuery wizard (thanks to the magic of Google) to extract the columns I need(which is pretty much all of columns from the original data source, but the extracted data is essentially the same…so of course the end result is that I'm at thesame place with the pivottable construction.

The default code that PowerQuery gave me when extracting the columns I needed (following the steps in a Microsoft PowerQuery tutorial) looks nothing like that you provided.

Without knowing what your code is doing and what the parameters are, I'm afraid it's just Greek to me and doesn't help me figure out how to get the data to present the way I am hoping.

Thanks for your followup.

 

[sandy666]

maybe it will help : Movie how to... (it should play on every kind of Windows)

excel file

btw. M-code you can see in PowerQuery editor - Advanced Editor

 

[trikky]

maybe it will help : Movie how to... (it should play on every kind of Windows)

excel file

btw. M-code you can see in PowerQuery editor - Advanced Editor

Had a quick look at the video when I got home - I'll take a closer look when I'm at the office again, with the spreadsheet in front of me.

Appreciate the effort you went to, to assist. I can see a couple of areas where I went slightly off track.

Cheers!