Importing files from a folder using Power Query

Power Query offers lots of options when it comes to importing data. Importing files from a folder is one of them, and it can result in significant time saving as multiple files can all be transformed based on the applied query steps. Let’s have a look at one practical example.

Our fictive company saves monthly sales reports, in a specific shared folder. All data is saved as separate .xlsx files for the concerned month. The month name and the year are always included in the filename. We currently only have data available for the first 3 months of 2023:

All of the reports contain the following 3 columns:

Column A displays the sales ID, column B displays the name of the sales representative (either James or Olivier), column C displays the region (either Asia or Europe), and column D displays the sales amount in EUR. Every first day of the month, after a new sales report is added to the folder, some colleague is manually copy pasting the data into a Masterfile and updating their formulas. This is a risky and inefficient process and Power Query offers the solution.

Importing the data from a folder

First we start by selecting from folder in the get data section in Power Query and paste the folder path:

Power Query then automatically generates the following columns based on the folder path and file details:

The first column Content contains the contents of the separate Excel file. Column B displays the file name and the rest of the columns are specific details of the separate Excel files. In order to read the data, we have create a custom column and use the following function:

Excel.Workbook([Content])

This particular function reads all of the objects from the separate Excel files and it will generate this new column:

The new column contains nested tables with all of the objects from the Excel files. We can expand the contents and Power Query will show all of the objects that it has read from the Excel files. 5 new columns are generated:

The column Item displays the items that it could read from the Excel files and column Kind displays what time of item it concerns. Power Query is able to read Tables, named ranges, dynamic arrays and sheets. In this example we know that the data that we are interested in, is always located in a sheet named Sheet1. To make sure that Power Query always imports the correct data, we apply a filter on the Item column and filter only on Sheet1 values:

After applying the filter, we are left with only relevant records. Now that we have applied our filtering, we can delete all columns besides Name and Data. The Name column contains the name of the file which we can use to determine the sales month and year. The column Data that was created by Power Query contains nested tables with the actual contents that we are interested in. Now we can proceed with expanding the data:

After expanding, we have all the data, of all three Excel files combined:

Now we can promote the first row as headers and validate that all data is included by checking the available values in column A:

Based on information above, we can confirm that we have successfully combined all the data from the three separate Excel files. However, as Power Query combines all data in the Excel files, it also combines the header data. That means that the Sales column does not only contain the sales value, but actually also contains 2 “Sales” values. As the Sales column has a numeric datatype combined with the fact that Power Query is datatype sensitive, there will errors in the dataset. We can fix this by applying a Remove Errors step on the Sales column:

This step will remove all the errors based on the datatype of the Sales column, so it will remove all records that are not a numeric datatype. The data can now be used and analyzed. If there will be new files added to the folder that we have linked our Power Query to, they will be automatically included once the query is refreshed. This means that the transformation will automatically by applied to all files in the folder based on our query steps.