Power Pivot 2013 Using SharePoint 2013 List as Source Data

Background:

On a SharePoint 2013 site, there are lists that have the exact same columns but due to their size I had to create a different list for each year. These are essentially my data lists or tables that end users enter data into. These lists have look up columns in them, referencing columns from look up lists or tables located on the same site.

Goals:

1. Create a Power Pivot Gallery on SharePoint 2013 that is able to leverage the "manage data refresh" feature using the SharePoint 2013 List data that is on the same SharePoint 2013 site.


2. Combine all the data lists/tables into one table once in Power Pivot 2013. The Look Up lists/tables will remain separate.

Challenge:

Being able to merge or do a union query to the "data lists" and still maintain the ability to leverage the "manage data refresh" feature. This is a tactical, quick fix solution, and I cannot use SQL Server and am operating under the assumption that I do not have Access Services 2013 as an option.

Failed Attempt #1:

What I already tried was linking the lists to an Access 2013 database and did a Union query from Power Pivot which merged the data from the different data tables as I wanted it to.

Then I uploaded that Access Database to a document library on that same site, thinking that I could maintain the ability to leverage the "manage data refresh" feature but I was wrong.

Any thoughts or suggestions?