Results 1 to 9 of 9

Thread: Pivot Table - Multiple Column Headers Merging After Filtering

  1. #1
    New Member
    Join Date
    May 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Pivot Table - Multiple Column Headers Merging After Filtering

    Hi everyone,

    I am having issues with a pivot table merging 2 column headers and count of values together when I filter out Blanks.


    I've created an example workbook of the source data, what I'm doing (which is definitely wrong), and an example of what I want it to look like. https://www.dropbox.com/sh/1wky1or8a...Ngz40tZ6a?dl=0

    On top of this I need it to be able to refresh the pivot table without reformatting as part of a macro (I can write in the refresh)

    I appreciate any help or advice you have! Thanks!

  2. #2
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    1,682
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Pivot Table - Multiple Column Headers Merging After Filtering

    is that what you want
    (with PowerQuery aka Get&Transform)

    source Pivot with PQ
    Area Store RB TMT Count of Value
    Area 1 Store A Action Required RB RB RB TMT TMT
    Area 1 Store B Action Required Action Required Action Required Reminder First Day Action Required Reminder
    Area 1 Store B Action Required Area 1 Store A
    1
    Area 1 Store C Action Required Area 1 Store B
    1
    2
    Area 1 Store C Reminder Area 1 Store C
    1
    3
    2
    Area 1 Store C Reminder Area 2 Store D
    1
    1
    2
    Area 1 Store C Action Required Area 3 Store E
    3
    Area 1 Store C Action Required Area 3 Store F
    1
    1
    1
    Area 1 Store C Action Required Area 3 Store G
    1
    1
    Area 2 Store D Action Required Action Required
    Area 2 Store D Reminder
    Area 2 Store D Action Required
    Area 3 Store E Action Required
    Area 3 Store E Action Required
    Area 3 Store E Action Required
    Area 3 Store F Action Required
    Area 3 Store F Reminder
    Area 3 Store F First Day
    Area 3 Store G Action Required Action Required
    I know you know but I don't know what you know



    Post your Excel version (PC / Mac?), also link to shared excel file with representative example of source data and expected result.
    impossible things we do on the spot. for miracles you need to wait for a while

  3. #3
    New Member
    Join Date
    May 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Pivot Table - Multiple Column Headers Merging After Filtering

    Quote Originally Posted by sandy666 View Post
    is that what you want
    Yes! I don't have the Power Query add on but I knew I had done this before in (normal) BI so was going crazy trying to find the answer, thank you!

  4. #4
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    1,682
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Pivot Table - Multiple Column Headers Merging After Filtering

    You are welcome
    I know you know but I don't know what you know



    Post your Excel version (PC / Mac?), also link to shared excel file with representative example of source data and expected result.
    impossible things we do on the spot. for miracles you need to wait for a while

  5. #5
    New Member
    Join Date
    May 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Pivot Table - Multiple Column Headers Merging After Filtering

    Hi, do you have the steps for how you achieved the pivot? I just can't make it work

  6. #6
    New Member
    Join Date
    May 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Pivot Table - Multiple Column Headers Merging After Filtering


  7. #7
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    1,682
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Pivot Table - Multiple Column Headers Merging After Filtering

    Quote Originally Posted by Jhuck View Post
    do you have the steps for how you achieved the pivot? I just can't make it work
    Look at this file: s666-PQ-PT-Store pivot table test
    I know you know but I don't know what you know



    Post your Excel version (PC / Mac?), also link to shared excel file with representative example of source data and expected result.
    impossible things we do on the spot. for miracles you need to wait for a while

  8. #8
    New Member
    Join Date
    May 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Pivot Table - Multiple Column Headers Merging After Filtering

    Perfect! Thanks and I'm sorry for being dumb!

  9. #9
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    1,682
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Pivot Table - Multiple Column Headers Merging After Filtering

    You are welcome

    Now you can hit Like/Thanks button in the posts which helped you

    Have a nice day
    Last edited by sandy666; May 15th, 2019 at 11:49 PM.
    I know you know but I don't know what you know



    Post your Excel version (PC / Mac?), also link to shared excel file with representative example of source data and expected result.
    impossible things we do on the spot. for miracles you need to wait for a while

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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