Merge 3+ datasets with identical structure to summarize content by Pivot table

Hi

I have several data sets that that have the same column headers (product portfolio data, split by categories into different workbooks) and the same structure and want to merge them to create summaries in a pivot table that I can slice the way I need to look at the data (e.g., by "Category 1" level, "Category 2" level, etc..). The messy way would be to just copy/paste all data in one master set and then create one pivot from it. The problem is that this a) takes a lot of time since I have several data sets and b) the content of these data sets is constantly changing, so I would need to constantly update the master data set by replacing updated sub sets.

I was hoping that I can use PowerPivot for that which is apparently not the case - as I understand PowerPivot is only being used to link data sets that contain a column with an identical key to then basically do on a larger scale what a vlookup would do.

Can somebody tell me how to do that? Is there a way to do this with PowerPivot? I was trying to find a thread/link to help me out but didn't find a solution that actually solves my problem. If somebody just has a useful link that he could direct me to it's also fine - I can read

Thanks
Til