In this post we’re going to find out how to import data from multiple files in a folder using Power Query. To do this we will need certain conditions to be true.
We’ll need Power Query installed. Read this post to find out how to install it.The data structure in each file must be the same.Same number of columns with the same column headings.Data is on the same sheet name in each file.We will need all the files we want to import located in the same folder.
For our example, we’re going to import 4 Excel files all from this location.
Each file is structure the same way. The column headings are all the same and in the same order. The data is in a sheet called Sheet1 for each file. Only the data is different for each file.
Step 1: Setting up the import query for one file.
Now we will set up the import query for one file.
Now choose a file to import. You can choose any of the files in this step as we are creating an import query that will work for all the files and all the files are structurally the same.
A preview of your data will appear in the Navigator window.
Step 2: Edit the query.
Power Query will create the import code (in a custom language called M) behind the scenes, but we will need to edit it slightly.
We can now edit the M code. We have just changed the query to a function that takes a file path and name as inputs then returns the data from that file path and name.
Step 3: Create a query on the folder containing your files.
Now we will create a query on the folder containing all our files.
This type of query outputs data about all the files in a folder such as the file name, location, date modified etc… A list of your files will be previewed and we can then press the Edit button if this preview looks ok.
Step 4: Add a column with your custom function.
Now we will add a column to this query. This will call the function we created in step 2.
Now we need to expand the GetData column we just created to show its results.
Now our data will be imported from all the files in our folder and combined into one table. If we add files to our folder later on, we can import these also by simply refreshing the query. Wow, the POWER!
