Pivot table not showing the correct output (instead of the column names showing dates

Good afternoon,

I have attached the sample file. The original file has more columns and data but the sample file represents the main problem I found in the original. Basically, We have healthcare professionals name as in column A in the sample file. Lets say they review a patient and if they start that patient on Inclisirian, we can see the date they were started in that column and the same goes for the columns C (for Statins). In the original data, either we have a date in those columns or they are blank.

I want a report to show the following instead of the output shown in the attachment. It shows dates in the column header in the pivot table which I don't want. My desired output:

Name Inclisiran Statins
Jignesh 3 2
Daxesh 1 1
Viklo 1 1

Can we breakdown further by months as well to see who has done what in each month?

Thank you. Sorry, I can't attach the original.

Hi,

I would suggest first amending your source table within Power Query, selecting the Name column and then Unpivot Other Columns. Then Close & Load this table as Connection Only and check the Add to Data Model box. This will produce a layout which is more amenable to Pivot Table analysis.

You can now construct a simple Pivot Table from this new table and add a Slicer for the dates.

Regards

you can try change layout to this with Tabular Form or Compact Form

btw. try to use shorter headers in your source table

Name	Inclisiran started	Statin started	Count of Inclisiran started	Count of Statin started
Daxesh	(blank)	24/05/2022		1
		(blank)
Jignesh	23/05/2020		1	1
	30/06/2022		1
	(blank)	(blank)
Viklo	01/01/2019	(blank)	1
	(blank)	(blank)
Grand Total			3	2

ptf.png

Hi XOR LX,

Thank you. I don't know about power queries. Could you please attach the solution so that I can follow how it works.
Is it possible to use months in my pivot instead of those dates?

like this?

Count of Name		Value
Name	Attribute	January	May	June	July	August	September	Grand Total
Daxesh	Inclisiran started						1	1
	Statin started		1					1
Jignesh	Inclisiran started		1	1		1		3
	Statin started		1		1			2
Viklo	Inclisiran started	1						1
	Statin started					1		1
Grand Total		1	3	1	1	2	1	9

or

Count of Name		Attribute
Name	Month	Inclisiran started	Statin started	Grand Total
Daxesh	May		1	1
	September	1		1
Jignesh	May	1	1	2
	June	1		1
	July		1	1
	August	1		1
Viklo	January	1		1
	August		1	1
Grand Total		5	4	9