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.
    Excel files in one folder
  2. Create new excel file, go to Data tab -> Get Data -> From File -> From Folder -> insert folder path -> OK.
    Get Data from folder
    Folder path
  3. Click on Transform Data button. This will launch Power Query Editor window.
    Transform Data
  4. Click on Add Column tab -> Custom Column.
    Add 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)
    Insert custom formula
    Then click OK. This will add column on the right most table.
    New column
  6. Click the little expanded icon Expanded icon on the new column (WorkbookObject), then click OK.
    Expand column
  7. On column WorkbookObject.Kind, click on drop down filter, then select only Table, then click OK.
    Select only Table
  8. On column WorkbookObject.Data, click the little expanded icon Expanded icon, then select the columns you want to insert on, then click OK.
    Expand Data column
  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.
    Delete unneeded columns
  10. Click Home tab, then click on drop down triangle on Close & Load icon, select Close & Load To….
    Close and Load To sheet
  11. Import Data window will be shown, select Table and choose Existing worksheet option, then click OK.
    Import Data to sheet
  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.

R. Bambang Widiatmoko

I'm an engineer and working in engineering consultant office. I have some experience in computer application in graphic design (CorelDRAW, Inkscape, GIMP), AutoCAD, and Office. I also like to read technology news.

You may also like...