Combine Tables with Identical Layouts sourced from different workbooks into a single pivot table

[kacker17]

I have been attempting to follow the instructions found on the below link to pull data from identical tables into a single pivot table using power pivot. Each table contains data from a different year-all 5 tables have the same exact column headers, just different data for each respective column. Having it all in a single file made the file too big to function adequately.

https://www.contextures.com/PowerPivot-Identical-Excel-Files.html

When I try to validate the new data sources in power pivot I get the following:

"The SQL statement is not valid. There are no columns detected in the statement."

One thread I read said it's because I have more columns in some of my sheets and that deleting the extra columns fixes the issue. I have checked this and verified all of my columns in the 5 tables have identical headers.

Any help you can offer would be super helpful.

KACKER

 

[sandy666]

try Alt+D+P

edit:
to be sure about headers, copy header row from first table/range and paste into all next tables/ranges

 

[sandy666]

I overlooked different workbooks so try PowerQuery (Get&Transform) with Append feature then you can create PivotTable directly from QueryTable

 

[kacker17]

Okay I'll give that a try and see if it works!