Combining various workbooks in a single pivot table

Hi Aditya,

Sounds like you are loading the combined data from all the sheets to the workbook. Instead you can load all the data to Excel data model. You have the option to load the data to the Excel data model in the "Load To" dialogue of Power Query as "Add this data to the data model". Once loaded into Excel data model, you can open the power pivot (Excel data model) window by clicking "Manage" command from "Data Model" group in "Power Pivot" tab. 

In this Power Pivot window, you have the option in "Home" tab to create a Pivot Table on top of the data in the excel data model. This Pivot table will be sitting inside your workbook like a regular pivot table, but this is lot more powerful and runs much faster.

Hope that helps..!!

Thanks :)

Balakrishna