In this sample query, you can make any changes and PQ will apply them to all the files in the folder before combining them to one gain data set. So let’s proceed by editing “Transform sample file from reports” query. Editing the Transform sample queryĪs you can see, the default combined query data can be useless for our situation. This is how your Power Query window could look. PQ will also create a query for all the merged data. Select that and proceed.Īt this point, Power Query will create a folder called “Transform sample” and places a few things in it. As we have two sets of consolidations, let’s start with the first one – travel details tab. Now you will get another screen asking you choose which tabs / tables you want to bring. Once satisfied with the list of files (don’t worry if you need to exclude some files, you can do that while editing the query by applying filters), click on “Combine & Edit”. Point to the folder path and Power Query will show all the files in that folder. Now go to “Get Data > From File > Folder” This can be a folder on your computer or on a network / shared drive. Start by placing all the expense reports in to one folder. Unfortunately, this alone will not be helpful for us as our file has two different tabs and we need to combine them separately 😉 This helps us easily get & combine multiple excel files in a folder.

Some of you may already know Power Query’s “Get data from Folder” feature.

Excel 2016 query editor apply filters