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)

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

 

[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