How to merge content of all files in folder with Power Query (Get and Transform)

This article describes the easy way how  to merge big number of sources (xlsx, csv…) from one folder.

We will use “Power Query“, whose name in Excel 2016 is “Get and Transform”. Power Query is not available for Excel 2007 or older.

What are we going to do:

• Create a new query from one of the source file
• Change this query to custom function
• Create new query, whose result is the list of all files from folder. Then we will run the function on all of the rows of this query.

Now it looks scary, but it is quite simple and it can be easily done in five minutes.

We will use the example of multiple teams in one company, reporting their timesheets in xlsx files. This files have to be merged into a single excel database.

One of them looks like this (lets say all of them have similar columns):

Now open the file where the data should be merged and create simple query from any of source files.

The query will be created, but not loaded. In View ribbon go to Advanced editor, where the query code can be seen.

We need to make two tiny changes so as to change it from query to function. At the beginning we will write the name of variable, and then we will replace the path by the name of variable. Like this:

---

(MyPath)=>

let
Source = Excel.Workbook(File.Contents(MyPath), null, true),
Table1_Table = Source{[Item=”Table1″,Kind=”Table”]}[Data],
#”Changed Type” = Table.TransformColumnTypes(Table1_Table,{{“Date”, type date}, {“Hours”, Int64.Type}, {“Activity”, type text}, {“Weekday”, type text}})
in
#”Changed Type”

---

Confirm and save. The function can be renamed, for example to Merged (in the pane on the right side).

Now lets create another query, from folder.

Select folder with source files:

The content of this query is the list of files (not their content…). 

The query has to be modified. Lets add new column and write this formula using the custom function:

• =Merged([Folder Path]&[Name])

Confirmed. In last column click on the double arrow in header and confirm.

Remove the unnecessary columns, if needed, and click on Close and Load.

Now the data is loaded, and when any of files is removed, added or modified, the result can be simply refreshed.