+ Reply to Thread
Results 1 to 7 of 7

Pivot table with multiple columns

  1. #1
    Registered User
    Join Date
    12-06-2018
    Location
    NL
    MS-Off Ver
    2016
    Posts
    4

    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
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    09-25-2015
    Location
    Nowy Tomysl, Poland
    MS-Off Ver
    2016
    Posts
    360

    Re: Pivot table with multiple columns

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

  3. #3
    Registered User
    Join Date
    12-06-2018
    Location
    NL
    MS-Off Ver
    2016
    Posts
    4

    Re: Pivot table with multiple columns

    Quote Originally Posted by stasinek View Post
    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
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    09-25-2015
    Location
    Nowy Tomysl, Poland
    MS-Off Ver
    2016
    Posts
    360

    Re: Pivot table with multiple columns

    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.

  5. #5
    Registered User
    Join Date
    12-06-2018
    Location
    NL
    MS-Off Ver
    2016
    Posts
    4

    Re: Pivot table with multiple columns

    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

  6. #6
    Forum Contributor
    Join Date
    09-25-2015
    Location
    Nowy Tomysl, Poland
    MS-Off Ver
    2016
    Posts
    360

    Re: Pivot table with multiple columns


  7. #7
    Registered User
    Join Date
    12-06-2018
    Location
    NL
    MS-Off Ver
    2016
    Posts
    4

    Re: Pivot table with multiple columns

    Thanks!!

    Issue solved, very helpfuyll.

    XK

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1