How to combine multiple files with Power Query (with no VBA and just 10 mouse clicks!)

The need to combine information from multiple files is one that most users of Excel will have come across at some point in the use of Excel. I've personally spent far too many hours aggregating data from multiple files, that are identical in structure, so that I can analyse larger datasets and provide insights into products and processes.

For anyone who has also done this and not yet discovered Power Query you'll probably be amazed how simple the process has become. I realise there might be some who will say "just use VBA, its easy once you learn how to code..." and they would be right. The method using Power Query provides a zero code solution that is an evolution of the Excel interface that many will already be familiar with.

In this example, I've created a sample file and created a number of duplicates of the file which I've saved in a folder. The folder contains only these files and i'd recommend you do the same if you're looking to try out this process. It is possible to use an existing folder with other files within it but this method is simpler.

If you need to do this in Sharepoint click here to see the process.

Once you have something similar the process you need to carry out is as follows:

• Open PowerBI or Excel, both of which contain Power Query. I'll be using PowerBI for this demo.
• Click on the button labelled 'Get Data', then 'more' and then when the following window appears, select 'Folder' and then click 'Connect'

• Then in the next window, enter the folder name which contains the files you'd like to combine. If you would prefer to navigate to the folder, use the 'browse' button, find the folder and click 'ok' and then click 'ok in the window shown below

• The following window will display the files identified in the folder and some options to load the files. In this example, we'll set the files to 'combine & load', this method will automatically combine the files and then load them to the data model.

• The next screen allows you to select the information you wish to combine using one of the files as a sample. If your data is a table, select the table from the option on the left. If your data is a sheet, select the sheet and then click 'Ok'.
• Once the above sequence has completed, the combined data will be added to the data model.in the example I've created, it is listed in the fields section of Power BI as shown here

• Now that the files are combines, one method to confirm the data has been loaded is to create a table showing the number of records for each file, this can be done using the 'table' visualisation and using the 'Source.name' value which is the filename as shown below
  
• An additional way to check the data is present and correct is to view it using the 'data' view located in the left hand section of the window, like this

And that's it!