Pivot table with multiple columns

Hi,

I'm struggling with this issue: create a Pivot table with multiple columns.

Attached file you see a number of rows with different categories. In the columns there are 20 options with different values.

Now I am trying to create Pivot table which is counting per category the unique values of all options.

I want to use a slicer to select the category and in the row section of the Pivot table the unique values.

For values of the Pivot table I want to count the number of unique values (like a take rate).

Hope my question is clear this way and of course that someone knows a easy way to do this.

Thanks,
EK

Hello,
This can be done, for example, with Power Query (changing the layout of the data, pivot table-friendly)

Originally Posted by stasinek

Hello,
This can be done, for example, with Power Query (changing the layout of the data, pivot table-friendly)

Hi!

Thanks for your reply. I adjusted the pivot a little but Sample II is the result I was looking for, based on your file!

But how did you manage the date in the sheet named "Tbl PQ" ?

My data is set up like in file "Sample" with more than 20.000 rows.

Again many thanks so far!

XK

I see that you have an Ex 2016 version so you have access to Power Query in the Data Tab.
Turn your data range into an excel table and add it as a source to Power Query. Select the first koulma - the right mouse button (already in the PQ query) and cancel the change of other columns. After data transformation, load the revised table into the worksheet.

OK, It is almost clear but..

I have done:
1. Excel sheet --> Insert table
2. In PQ: Get external data from this table
3. Select 1st column and freeze columns

I think this is what you said or not? And then I do not know what to do?
what do you mean by data transformation?
and how to load the revised table into the worksheet?

Thanks again,
XK

See this guide on YT:
https://www.youtube.com/watch?v=XQS5V6_AGn8

Thanks!!

Issue solved, very helpfuyll.

XK