Merging of data from all sheets from files in some folder

This article describes how to merge tables from all files in a folder – including all sheets. 

We will use it in a situation like this:

In the file where you want to have the merged table go to the Advanced editor in Power Query (Get and Transform) and insert this function. It gets data from all sheets in one file.

---

(mypath)=>

let

    Zdroj = Excel.Workbook(File.Contents(mypath), null, true),

    #”Result” = Table.ExpandTableColumn(Zdroj, “Data”, {“Column1”}, {“Column1”})

in

    #”Result”

---

Give some name to it, like MyFunction, and close it.

Create new normal query to folder, which creates list of all files in folder.

Edit the query and add this column. This means you will run the previously created function on all files.

=MyFunction([Folder Path]&[Name])

Be careful, the name of function is, like everything in Power Query, case sensitive. 

Now there is a new column. Go there and click on the double-sided arrow.

That´s all. .

Now you can remove the not necessary columns and keep only the information about where does the data come from.