Multiple pivots from same data connection

[lyambor]

I have multiple excel workbooks from different schools in our school district with food inventory data

Each school file has a separate sheet for each month with the monthly inventory data
Since the top of each of these files has some small pivot tables displaying current monthly data I named the actual data range cells to be viewed easily in PowerPivot

My question is:
I successfully used PowerPivot for the first month to create a pivot displaying multiple schools inventory data using the recommended SQL method for data connection

SELECT [BATESOCT].* FROM [BATESOCT]
UNION ALL
SELECT * FROM `C:\users\lyambor\desktop\tester\testsouthriver.xlsm`.[sroct]
UNION ALL
SELECT * FROM `C:\users\lyambor\desktop\tester\testoldmnorth.xlsm`.[oldmnorthoct]
UNION ALL
SELECT * FROM `C:\users\lyambor\desktop\tester\testlakeshore.xlsm`.[oct]

I have tried to edit the existing connection SQL script to simply change to the named ranges ie [nov]
Using the existing connection I have been selecting different named ranges as my table source
It appears to work when I execute the connection creating additional tabs thinking I can create new monthly pivots -but the monthly data is not coming in correctly


I am new to PowerPivot but I would think this is a fairly common request---

Basically I have multiple schools with a tab for each month in Excel (2010)
A simply want to use Power Pivot to create 1 file with a pivot for each month
displaying the schools in column labels
the Category food items in the row
and calculated sums for the orders in values
I did it great for the first month - using power pivot
how do I get the rest of the months data to pivot on

Any suggestions are greatly appreciated
Thank you

 

[Jacob Barnett]

This method should work - have you tried UNION instead of UNION ALL?

If you have duplicates across tables you may need to concatenate 2 or more fields together in the query to create a unique 'Key'.

 

[lyambor]

Thank you
I think I got it working
but that is an idea for my next step in our report process
Appreciate your time