Excel Turbo Logo

Dynamic Folders in Excel Power Query

Sometimes you want to make a project for your client or company to load tons of files from a folder. In this situation, you may have just some dummy data or partial files. Maybe you have a set of outdated files just to get the layouts (this actually happens a lot, because of the nature of data, that is always changing). But there is another thing common in projects: maybe you will need to alternate folders for specific needs, like test and production! Keep reading, because I'll teach you here how to do this.

We can repeat the queries with different folders without having to build them again

In this article, I'm going to cover 2 things that people asks me a lot: The first one is how to get data from several files in a folder. You will see how easy is to do this using one of the powerful data tool that comes within Excel: Power Query. Other topic that I'm going to describe step by step in this tutorial is how to easily switch folders used for queries in Excel. You will see that we can repeat the queries with different folders without having to build them again everytime we want to specify a new folder to get data. So, let's do it!

Step 1: Named Cell with Data Validation

The first thing you need to have for a well organized work is a named cell with a name easy to remember. Since I'm working with data extraction from a folder, the name that I'm defining for my cell is Extraction_Folder. Easy, right? Keep it in mind and follow my steps. You will see that if you remember the name, you will avoid having to close a screen to search for it, while you execute one of the steps ahead. If you don't know how to name the cell, it's simple: just click on the cell, go to the Names Box (the red rectancle of this image), write the name and press ENTER.

Step 1: Named Cell with Data Validation
Step 1: Named Cell with Data Validation

One thing that is not mandatory, but I strongly recommend, is applying Data Validation on this cell with the possible folders that you can use on this work. For some people, they will be different periods. For others, will be regions, customers, product categories, or even accounts or clients. Here in my examples I will alternate between Production and Test environments. I don't recommend leaving this cell entirely free without the validation, because the free writing may bring the possibility of failure due to mistyping.

Tip: Copy the folder path from this cell with Ctrl+C before the next step and save your time.

Step 2: Getting Data from the Folder

Inside the Data tab, just find the command inside the Get & Transform Data group that allows you to get data from several files inside a folder: Get Data, From File, From Folder.

Step 2: Getting Data from the Folder
Step 2: Getting Data from the Folder

Step 3: Choose the Folder

Here you type or select one of the valid folders of your task. It will be changed in the future, but to make your life easier, make sure the current folder is valid and it contains valid files. If you are reading all the instructions with attention, simply press Ctrl+V and move ahead.

Step 3: Choose the Folder
Step 3: Choose the Folder

Step 4: Transform

It's simple: Just take a look if the information about the files makes sense and press Transform.

Step 4: Transform
Step 4: Transform

Step 5: Combine Files

That's it: Press the Combine Files button to bring all the data together.

Step 5: Combine Files
Step 5: Combine Files

Step 6: Check the Content

Here you check if the content looks okay to you. Maybe it's a good idea to change the sample file in the first dropdown, where is now selected the first file. Try others and verify if everything is right. Once you finish to check it, just press OK.

Step 6: Check the Content
Step 6: Check the Content

Step 7: Remove the First Column or Modify it

Sometimes it's a good idea to track the source of the data. If you are working with periods, you will probably want to keep this information in a column. I just think that on this case you could modify this column, because having the filenames there doesn't look so good to me. You may spend a moment to remove the extension and the prefixo or suffix. In my example, I don't need this information, so I'm removing the first column. Bye, Source.Name!

Step 7: Remove the First Column or Modify it
Step 7: Remove the First Column or Modify it

Step 8: Open the Advanced Editor

Let's have some fun inside the Advanced Editor!

Step 8: Open the Advanced Editor
Step 8: Open the Advanced Editor

Step 9: Replace the Folder Path with a Variable

Here comes the trick: Replace the full path of the folder with a variable. If you never made a program before, just think of a variable as something that can vary (and it will). Don't forget about the quotes. You need to remove it, since you want that Power Query stop interpreting this as a text. The names of variables follow some rules and every language has its own commands (in this case, we see the M Language), so it's very wise to avoid choosing too obvious names that may exist in the language, like Files or Folder, for example. I'm choosing MyFolder as the name of my variable.

Step 9: Delete the Folder Path and Quotes
Step 9: Delete the Folder Path and Quotes
Step 9: Write the Name of a Variable
Step 9: Write the Name of a Variable

Step 10: Insert a Magic Line in the Code

Above the line of the Source, where you replaced a folder path with a variable, use this command below, making sure that in the place of the underscores, you write the same name that you gave for the cell in the first step of this tutorial (remember that I told you?):

MyFolder = Excel.CurrentWorkbook(){[Name="__________"]}[Content][Column1]{0},

On this image below, I'm firstly representing the MyFolder with a fixed full path for the folder, in a way that you can see how this mechanism is able to carry information into the parentheses of the next line of code. Can you see what MyFolder really is?

Step 10: See the Meaning of MyFolder
Step 10: See the Meaning of MyFolder

Now, let's apply the magic line in its full potential, including the name of the cell that you forgot: Extraction_Folder. Look:

Step 10: Inserting the Full Magic Line in the Code
Step 10: Inserting the Full Magic Line in the Code

Step 11: Load the Data

Confirm everything and see the data flowing into a new Excel worksheet.

Step 11: Load the Data
Step 11: Load the Data

Step 12: Play With the Spreadsheet!

How about changing that initial cell to another folder path to see what happens? Try that and refresh your query!

Step 12: Play With the Spreadsheet!
Step 12: Play With the Spreadsheet!

See here? It's working! Instead of loading 178 thousands rows, it's loading 78 thousands now. The numbers are also different, because the values are coming from different files from another folder. But the query is the same!

Step 12: Refresh the Query!
Step 12: Refresh the Query!

Did You Like this Content?

If you like this article, leave your contact in a form below. I'll be happy to send you more Excel tutorials, tips, and tricks. I really encourage you to use your own files while executing the steps above, but if you need some help with dummy data to practice, just reply my first message after you leave your e-mail in the form below. See you soon!

About the Author

Cristiano Galvão is Microsoft MVP in the Office Apps & Services category, awarded by his contributions mainly in Excel. He is the organizer of the Excel Weekend, the biggest and coolest Excel event in Latin America, and also the content creator of the Excel Turbo channel on YouTube, which is part of the Microsoft Creators program, curated by Microsoft. Cristiano is the technical reviewer of the most important Excel books translated from English to Portuguese, and has Excel courses published with the biggest investments firm in Brazil and with the biggest professional network in the world. Cristiano is an international consultant and is always ready to help you solve complex problems on corporate data.

Do You Want More Content from Excel Turbo?

Leave your e-mail:

Excel Turbo

Excel is a trademark of Microsoft Corporation.

Unauthorized reproduction of the content of this site is prohibited.

© 2018 GENECSIS - All the rights reserved.