I found this forum after a search led me to https://www.myonlinetraininghub.com/automating-emailing-pivot-table-reports. My end goal for this project is to create custom-named PDFs based on custom-formatted worksheets by category. 

I am concerned that I am not thinking about the data/design of my workbook and queries correctly/efficiently. I have been asked to use Excel to do it, rather than Access (which is where I am more comfortable/versed). I have been teaching myself Power Query and dabbling in Power Pivot and VBA, and am seeking some clarity from this community as to how best to leverage the various tools.

I have three reports that I merged in PQ (merged two then merged the third with the result). The result is multiple rows of detailed data with a code to group them with. 

I summed the rows based on the code and filtered for sums >0. This is the list of codes that need either one or two reports on the details that make up the sum.

I duplicated the sum query twice, and then filtered each for the categories of interest. This gave me the two detail queries that will provide data for my custom category worksheets.

I created a power pivot from the two detail queries, displaying the sums by category, and my idea was to somehow VBA loop through the items on the pivot table to 'call' the category worksheet and filter the data, then create the PDFs. I'm not sure this is possible? So I went another direction next...

My other idea for the PDFs was to load the detail queries to their worksheet, and then use a dynamic filter to create a dropdown to loop through with VBA. This is the method I think the above referenced article will tie into.

My questions:

The PD category sum is double what it should be...removed duplicates fixed but not sure why it was duplicated? (if you can see why that would be great…might be my understanding of the data that's the problem though as I'm new)

Is there a better way to get the queries I need that will be more efficient and faster (large data set so slow loading!)? (That will also address the doubling issue?!)

Is the second option for PDFs the best option or is there a better way entirely?

The customized worksheet views - how best to create them? It needs to be formatted/subtotalled etc very specifically. 

I have attached the sample workbook, sample data, and sample output reports. SAMPLE SDPD Workbook is the working file, others are raw data files used in Power Query.

Thanks in advance!