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.
- 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.
- Create new excel file, go to Data tab -> Get Data -> From File -> From Folder -> insert folder path -> OK.
- Click on Transform Data button. This will launch Power Query Editor window.
- Click on Add Column tab -> Custom Column.
- Type column name in New column name field, for this example WorkbookObject. In Custom column formula field, insert this formula:
Then click OK. This will add column on the right most table.
- Click the little expanded icon on the new column (WorkbookObject), then click OK.
- On column WorkbookObject.Kind, click on drop down filter, then select only Table, then click OK.
- On column WorkbookObject.Data, click the little expanded icon , then select the columns you want to insert on, then click OK.
- 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.
- Click Home tab, then click on drop down triangle on Close & Load icon, select Close & Load To….
- Import Data window will be shown, select Table and choose Existing worksheet option, then click OK.
- 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.