Combining Multiple Into one Pivot/PowerPivot

[VitoHGrind]

Hi MrExcel!

I'm having trouble combining multiple sheets of data into one pivot. My data comes out of database and is a "snapshot in time" for a given date. I've combined these outputs into a master file where each sheet represents results for a date. The goal is to be able to quickly show the trend of a given performance metric over time for a specific row element.

I've achieved this (more or less) with PP using this guide:
http://www.contextures.com/PowerPivot-Identical-Excel-Files.html

However, I have trouble when I get to this section with SQL:

SELECT [EastSales$].* FROM [EastSales$]
UNION ALL
SELECT * FROM `C:\_TEST\WestSales.xlsx`.[WestSales$]

If I start in the master file, create a connection to a specific sheet within the same master file, open PP --> Existing Connections-->Select connection from "Workbook Connections"-->etc, this works without a problem. When I follow the instructions to enter the SQL statement and reference again the same master file, I get this error:

OLE DB or ODBC error: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done..

If I do the entire process from a separate workbook, it works. This means that the data is in one file, while the pivot table which is based on that data is in another file. Obviously, that doesn't make sense. Asking around the office, we're thinking the problem may be with self-referencing. Is there a step that I'm missing or is there an overall better process to achieve what I'm trying to do? Thanks for any help!