Combine Multiple Power Query Excel Files

This tutorial will show you how to combine table from multiple excel files produced from power query into one table also with power query.

How To

1. Save all excel files, that already have power query table inside of it, to be combined into one folder. One file can also has multiple sheets contain power query table.
   
2. Create new excel file, go to Data tab -> Get Data -> From File -> From Folder -> insert folder path -> OK.
   
   
3. Click on Transform Data button. This will launch Power Query Editor window.
   
4. Click on Add Column tab -> Custom Column.
   
5. Type column name in New column name field, for this example WorkbookObject. In Custom column formula field, insert this formula:
   Excel.Workbook(File.Contents([Folder Path]&"\"&[Name]),null,true)
   
   Then click OK. This will add column on the right most table.
   
6. Click the little expanded icon on the new column (WorkbookObject), then click OK.
   
7. On column WorkbookObject.Kind, click on drop down filter, then select only Table, then click OK.
   
8. On column WorkbookObject.Data, click the little expanded icon , then select the columns you want to insert on, then click OK.
   
9. Select columns which unneeded, for these examples are: Content, Name, Extension, Date accessed, Date modified, Date created, Attributes, Folder Path, WorkbookObject.Item, WorkbookObject.Kind, and WorkbookObject.Hidden; then right click -> Remove Columns.
   
10. Click Home tab, then click on drop down triangle on Close & Load icon, select Close & Load To….
    
11. Import Data window will be shown, select Table and choose Existing worksheet option, then click OK.
    
12. The tables from all excel files will be inserted into one big query table.

That’s all folks, I hope this tutorial will help you and make your works more efficient.

    Tweet