Pivot Table - Multiple Column Headers Merging After Filtering

[Jhuck]

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/1wky1or8aj1j7l7/AACD_KIYI5-NUPK_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!

 

[sandy666]

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

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]source[/td][td][/td][td][/td][td][/td][td][/td][td]Pivot with PQ[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Area[/td][td=bgcolor:#5B9BD5]Store[/td][td=bgcolor:#5B9BD5]RB[/td][td=bgcolor:#5B9BD5]TMT[/td][td][/td][td=bgcolor:#DDEBF7]Count of Value[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Area 1[/td][td=bgcolor:#DDEBF7]Store A[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Action Required[/td][td][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]RB[/td][td=bgcolor:#DDEBF7]RB[/td][td=bgcolor:#DDEBF7]RB[/td][td=bgcolor:#DDEBF7]TMT[/td][td=bgcolor:#DDEBF7]TMT[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Area 1[/td][td]Store B[/td][td]Action Required[/td][td]Action Required[/td][td][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Action Required[/td][td=bgcolor:#DDEBF7]Reminder[/td][td=bgcolor:#DDEBF7]First Day[/td][td=bgcolor:#DDEBF7]Action Required[/td][td=bgcolor:#DDEBF7]Reminder[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Area 1[/td][td=bgcolor:#DDEBF7]Store B[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Action Required[/td][td][/td][td]Area 1[/td][td]Store A[/td][td][/td][td][/td][td][/td][td]

1​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Area 1[/td][td]Store C[/td][td]Action Required[/td][td][/td][td][/td][td]Area 1[/td][td]Store B[/td][td]

1​

[/td][td][/td][td][/td][td]

2​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Area 1[/td][td=bgcolor:#DDEBF7]Store C[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Reminder[/td][td][/td][td]Area 1[/td][td]Store C[/td][td]

1​

[/td][td][/td][td][/td][td]

3​

[/td][td]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Area 1[/td][td]Store C[/td][td][/td][td]Reminder[/td][td][/td][td]Area 2[/td][td]Store D[/td][td]

1​

[/td][td]

1​

[/td][td][/td][td]

2​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Area 1[/td][td=bgcolor:#DDEBF7]Store C[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Action Required[/td][td][/td][td]Area 3[/td][td]Store E[/td][td][/td][td][/td][td][/td][td]

3​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Area 1[/td][td]Store C[/td][td][/td][td]Action Required[/td][td][/td][td]Area 3[/td][td]Store F[/td][td][/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Area 1[/td][td=bgcolor:#DDEBF7]Store C[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Action Required[/td][td][/td][td]Area 3[/td][td]Store G[/td][td]

1​

[/td][td][/td][td][/td][td]

1​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Area 2[/td][td]Store D[/td][td]Action Required[/td][td]Action Required[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Area 2[/td][td=bgcolor:#DDEBF7]Store D[/td][td=bgcolor:#DDEBF7]Reminder[/td][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Area 2[/td][td]Store D[/td][td][/td][td]Action Required[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Area 3[/td][td=bgcolor:#DDEBF7]Store E[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Action Required[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Area 3[/td][td]Store E[/td][td][/td][td]Action Required[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Area 3[/td][td=bgcolor:#DDEBF7]Store E[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Action Required[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Area 3[/td][td]Store F[/td][td][/td][td]Action Required[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Area 3[/td][td=bgcolor:#DDEBF7]Store F[/td][td=bgcolor:#DDEBF7]Reminder[/td][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Area 3[/td][td]Store F[/td][td]First Day[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Area 3[/td][td=bgcolor:#DDEBF7]Store G[/td][td=bgcolor:#DDEBF7]Action Required[/td][td=bgcolor:#DDEBF7]Action Required[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

 

[Jhuck]

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!

 

[sandy666]

You are welcome

 

[Jhuck]

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

 

[Jhuck]

Updated dropbox link https://www.dropbox.com/s/b2klisuxufiz6i7/Store pivot table test.xlsx?dl=0

 

[sandy666]

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

 

[Jhuck]

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

 

[sandy666]

You are welcome

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

Have a nice day