In my training sessions, I often have learners practice loading multiple datasets into Power Query and the Data Model, one file at a time. A common question arises: how can we load several datasets simultaneously? Here’s an answer.
To follow along, download the exercise files below:
To effectively manage all the files you want to load into the Data Model, ensure they are located in the same folder, such as in the exercise folder provided above. Make sure to unzip the folder and place it in a location that’s easy to find in your workbook.
Next, head to Data > Get Data > Query Options:
This allows you to customize some of the internal settings of your Power Query Editor. Go to Data Load under Global settings, and then under Default Query Load Settings, set it to Load to Data Model and Load to worksheet by default. I’ve selected Load to worksheet because if we are using Power Pivot, the data sources we import are typically intermediary and primarily relevant within the context of the Data Model. Click OK to apply these settings.
You may need to save your workbook and/or restart Excel for these changes to take effect.
Next, head back to Data, and this time From File > From Folder.
Navigate to the exercise folder’s location and click OK. You won’t see any files listed in the explorer because we are searching for folders and subfolders, not individual files.
You should now see all the files listed in the folder. There are some hidden files that originate from this folder being stored on GitHub, which you can safely ignore. We want to focus on the three Excel workbooks. Go ahead and click on the first one and select “Add as New Query.”
You will now see a file icon in the data area. Go ahead and right-click on the file name to confirm that it is an Excel workbook.
We have now drilled into the data sources found in this workbook. You’ll notice there appears to be one table in the workbook. Go ahead and click on “Table” under Data to drill in and view the actual dataset in the workbook.
The first row of data in the orders table are the headers of this dataset, but Power Query might not have automatically detected that. If that’s the case, go to the Home tab in the Power Query editor and select “Use First Row as Headers” to promote the first row to the header.
Awesome work! Now, return to the superstore query and apply the same process to the next workbook, people. This one contains multiple worksheets, so make sure to check which one actually has the data you need.
For a preview, click on the cell just outside the green Table hyperlink on each sheet to see a preview below. It appears that the data we want is in the first worksheet, although we have the same issue with the headers not being promoted. Go ahead and promote those headers too once you’ve imported the data.
Finally, repeat the process for the last query. Once you’re done, it’s time for some cleanup. I’ll have you double-click on each query’s name to change it from the filepath to just the name of each individual dataset: orders, people, and returns, respectively.
Lastly, you can delete the superstore dataset by right-clicking it and selecting delete. Don’t worry if you need to add another dataset from this folder. You can always recreate the process by starting with a copy of one of these queries.
Your Power Query editor should now look like this:
Finally, go to File > Close & Load > Close & Load To with any of these queries selected, and you’ll notice that our defaults are indeed set to create a connection only AND to add to the Data Model. Nice work!
From here, navigate to Power Pivot > Manage Data Model, and you’ll see that all tables have been added to the Data Model in one go. This is a great timesaver, and we’ve also learned how Power Query can efficiently handle entire folders of files. Amazing!
What questions do you have about setting up your data quickly for Power Query and Power Query for Excel? Let me know in the comments. If you’d like a basic overview of these tools be sure to check out my book Modern Data Analytics in Excel:
Leave a Reply