Combining files

Power Query gives us the ability to combine multiple files which belong to one folder. These can be TXT or CSV files, but in practice we will usually combine several Excel files. This is a very powerful option that can save us a lot of time. At the same time, it brings with it some challenges, which you will be able to learn more about in the following text …

I often advertise the Mačkov podrum („Cat’s Cellar“) winery in my examples, so this time I created three files of identical structure with the names of their wines. Each contains one structured table with three columns: COUNTRY, ITEM, SALES (EUR), which respectively contain the country in which the sale was made, the name of the items (wines) and the amount in EUR. The amounts are completely fictitious, though I used prices from their site when calculating.

For starters, to combine files, they all have to be closed. Then it is important to emphasize that all tables that we’re combining must have the same structure. The name of the worksheet, as well as the name of the table in which the data is located also apply to these rules. The import process begins by selecting the option to import files from a folder. With Excel 2016, you need to go to Data ribbon, then choose the appropriate option from the New Query menu. If you are using Excel 2019 or Office 365 this menu is now called Get Data. You can see the differences in the choice of the picture:

After you start this option, a window will open in which you need to enter the path to a folder from which we are retrieving files we will combine. When selected, a new window will appear where you can see the files in the specified folder. There are several options at the bottom of the window. The Combine menu contains options Combine & Edit, Combine & Load, and Combine and Load to. The Load option only loads the selected files, the Edit option enters the Query Editor, and the Close option cancels the operation. The best choice is to open the Query Editor, which is accomplished by clicking Edit.

The selected files will appear in the editor window. Clicking on the Combine Files option we’ll combine multiple files into one. A window will appear where you should select a table which structure  should be used in combining process. After confirming the action we will combine the three files into one.

Now all you have to do is delete the first column, which shows the source from which we retrieved the data, and then click Close & Load to load the consolidated table. If you change the data in one of the Excel files that belong to source folder, after clicking the Refresh button the consolidated report will be updated.

This would be an ideal scenario, but in practice, Power Query could not recognize the table structure we want to import. In this case, we need to do the combination in a slightly different way. After selecting a folder and entering the editor we need to add a new column by selecting Custom Column. As a formula, we enter the expression:

Excel.Workbook ([Content])

This allows us to import the contents of the files. After confirming the selection, a new column named Custom will appear, with Table contents. Clicking on the icon in the upper right corner of a column Power Query allows us to expand the spreadsheet, and by selecting Data option, we emphasize that we want columns that contain data to be displayed.

After running this action new columns will appear. They contain data from all three files we wanted to combine. With further transformation, through several steps, we will get desired dataset.