Not sure how to describe - Pivot Table setup question

Updating to include sample dataset & format excel file

Further, not sure if this is possible given the dataset/survey responses.


I'm not even sure how to go about describing what I want, so I'm not sure how to look up what I want in past answers. Apologies if this information is somewhere else on this forum.

I have data from a survey that I want to put in a table with the ability to filter by service area/line. I'm pretty sure I need to alter my data using Power Query, but I'm not sure how to alter the data so that it can pivot in the way I need.

Any and all help is much appreciated.

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

Using Power Query, create two queries.

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F	G	H	I	J
1	Attribute	25%	50%	75%	100%			Attribute	No	Yes
2	Baby Supplies	9	0	1	3			Department Technology	16	11
3	Client Furniture	7	4	4	4			Department Vehicles	20	7
4	Clothing	6	5	5	4			Do you currently independently seek out and/or accept in-kind donations in order to meet client or administrative needs?	10	3
5	Food	5	6	3	5			Facility Furniture	17	10
6	Household Items	7	5	3	5			office/maintenance supplies	17	10
7	Medical Equipment	12	2	2	1
8	Personal Care Supplies	5	6	5	4
9	Pharmacy Items	7	4	2	6
10	Services in Other Languages	10	2	0	0
11	Technology Access	5	6	4	2
12	Transportation	6	6	4	5

Sheet: Table1

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.