Search code examples
excelpowerpivotpowerquery

Consolidating multiple tables from different workbooks with same fields into one table using PowerPivot

I have about 100 workbooks with a table in each workbook. Each table contains the same fields, but with different data. I am looking for an efficient way to consolidate all these tables into one Table on one excel workbook using PowerPivot. Is this possible?

Solution

  • Your solution is with Power Query. You simply have to go to Data tab -> New Query -> From File -> From Folder, and select the folder which contains all your excel files.

    From there, click on Edit and this will bring you to the Power Query interface, where you can join all the files together. This video is an excellent guide to achieving this. If done right you should have them all combined in minutes.

    Once everything is combined in Power Query, you can easily load the data into Power Pivot.