Excel Unplugged

Get data from Folder in Power Query


Following a wide demand, I also wrote a post about getting data from Excel files in a folder. You can read it here.

It seems that lately Excel users can be separated into two groups. One group that gets the new Excel (2010 or 2013) but still uses the application as if it’s Excel 2003 and those are users that may have heard of the whole Power BI thing but think that the whole Power BI story can only benefit certain people. That is not true. Power BI is all in all a set of brilliant commands. This post will show you only one of them and that is a Get Data From Folder command in Power Query. But it will also show you how Get Data From Folder command in Power Query can make your life easier. Take the time and read this post and think about how to implement this in your work. Trust me, you will not regret it! Here’s what this post will talk about.

If you get new data periodically in any form (txt, csv, xlsx…) then you know that what that means is that every time you have to go through the same process to get that data into Excel. Now this will be reduced to copying that file to a certain folder and clicking refresh in Excel. It doesn’t get much better than that. But first, let’s explain what is Power Query.

Power Query is an Excel Add-In that was created to help you get data into Excel or Power Pivot. It’s part of Microsoft Power BI and is even incorporated into the new Microsoft Power BI Designer. Everything you read about in this post refers to Excel 2010 and Excel 2013 for which Power Query is available. And now for the Get data from folder command.

When I first found this command, my enthusiasm about Excel doubled over night (and that is saying a lot). Here’s where it can really help you. Working with Excel will sooner or later get you in a position where you will be repeating one and same actions periodically. In the beginning that is great, but in time you would much rather be creating great dashboards and charts and not repeating the same imports or copying over and over again. Before Power Query was “born”, the only tool at your disposal for easing of the process was VBA. You could write a macro that did the import and accounted for all specifics in your data. But now, things got even better. What follows is a nice example of that.

Let’s say that every month, we get a new txt file that is just an export of data that we want in Excel so we can analyze it. This is how those txt files look like.

Get data from Folder in Power Query

So every month we have to remove the top six rows, correctly import the columns and add them to previously imported data and analysis. Here’s how you do it with Get data from folder command in Power Query.

On the Power Query tab select From File and then From Folder. This will open a Choose a folder dialog box, where you can choose any folder you like (even folders on SharePoint).

Get data from Folder in Power Query

Once you choose the folder, you get what will soon become your favorite window in Excel. It’s the Query Editor Window. And here the fun begins.

Get data from Folder in Power Query

The above picture also shows two buttons we will use magnified, since one hides the dropdown menu of most used commands in Power Query. And we will use many of them during this process. But the other is even more important in this first stage and that is the double down arrow in the Content column.

The reason we need this button is that at this point, we are seeing the list of files in the folder but not the content of those files. But pressing this button will show exactly that.

Get data from Folder in Power Query

Here’s what we get after pressing it.

Get data from Folder in Power Query

And now the fun begins. We will do some data transforming by mostly using the dropdown menu button we talked about before.

Get data from Folder in Power Query

First off, we will delete the top six rows. So from the dropdown menu we choose Remove Top Rows…

Get data from Folder in Power Query

As the three dots at the end of the command suggest, you get a dialog box, and all that it needs from you is the number of top rows, you wish to remove (in our case that is 6).

Get data from Folder in Power Query

After that we will choose a simple Use First Row As Headers command.

Get data from Folder in Power Query

Now if you can imagine, this took care of the first six rows from the first txt file. But the same six rows from other txt files in that folder are still there, appended bellow. So the next step must be removing those. We will be using the filter in the name column to do that. First a little trick. At the beginning, the filter only shows you a limited amount of records and you must use the Load more command shown below.

Get data from Folder in Power Query

Now we remove the blanks (since all rows contain the name, than these must be the six rows at the beginning of all txt files)…

Get data from Folder in Power Query

…and then remove the rows containing Name (these are the header rows of other txt files)

Get data from Folder in Power Query

Now we have the data we need, we just have to tell Excel what that data is. So we set the data types of the columns. So we select the column and go to Home/Data Type and select the appropriate data type.

Get data from Folder in Power Query

With that done, we attend to the specifics in our data. In the City column, a question mark has replaced the apostrophe so we should revert those changes. We will use the Transform/Replace Values command.

Get data from Folder in Power Query

The dialog box will remind you of a classic Find and Replace Window.

Get data from Folder in Power Query

And this is what you get

Get data from Folder in Power Query

Now we will use the Close & Load command.

Get data from Folder in Power Query

Ok, so now we did all that we do every time we get a txt file. More or less a same amount of work, so the question is, what we have gained or what is so special about this.

First and foremost, at this point we have imported four txt files at once and now have 4000 rows. But now we just got the new txt file with new data.

Get data from Folder in Power Query

All we need to do is to copy that file to the same folder as the others.

Get data from Folder in Power Query

And just right click the data in Excel and choose Refresh

Get data from Folder in Power Query

And right away we get 1000 new rows from the new txt.

Get data from Folder in Power Query

So Power Query will take all the files in that folder and repeat all the steps that we have defined before. Brilliant. But there’s more. What if we get a txt file that needs a new rule or a change in the old set of rules? All we need to do is to right click the Query on the right and choose Edit and we are back in the Query editor and on the right you can delete or modify any of the steps that we defined in the original query.


Get data from Folder in Power Query

Pure Brilliance. But if the new txt file would be totally different than the previous ones, you could define a new query for that one and then use the Append command to add the new data to our old Query.

Now do you believe me, that this is truly a game changer. Just imagine. You only do the hard work once and then it’s just a matter of refreshing your data. And that is why its called POWER BI and POWER QUERY 🙂



Comments 29

  1. Fran Reed says:

    Thank you Gasper.. Great article.. I am just starting the PowerBI tool journey – figuring how to incorporate with client work. awesome. This will get me started.

  2. Ben says:

    HI Gašper, I have followed your article and everything works almost perfectly. I’m finding that when I add in another months data and refresh not all of the dates get reformatted but display as the general number format. It means I end up having.

    Any ideas on this. I’m using my own data for this.

    1. There has to be something in that column, that is not a date (or an equivalent serial number)… could just be that the headers are still there, could be a strange number that Excel doesn’t recognize, but some piece of data has to prevent Power Query from applying the Date format. If the data is not sensitive, you could send it to me and I will have a look.

      1. Ben says:

        That will be great.

        What email address do I need to send it to.

  3. Larra says:


    This is great!

    I need to have the filename on every row, is therenany way to accomplish this?

    1. @Larra
      Sure there is… Here is a Ken Puls version of this.
      If you need any additional explanation just ask.

  4. Michael Amato says:

    Is there a way to get a folder that is actually on sharepoint?

    1. Hi,

      the Get Data from a Folder will not work with SharePoint. The Get data from web will, but the question is can you use get data from web, input the URL of your SharePoint library and then input your credentials and get the file list? Last time I tried that and it didn’t work… Now Power Query does have a SharePoint list as a source in the From Other Sources section but that doesn’t give you anything even remotely resembling the Get Data From Folder command functionality.

      1. Takahiko Doi says:

        Generally, it seems that Power Query is not support SharePoint folder as a data source, but you can use following code in Advanced Editor.
        Source = SharePoint.Files(“https://xxxxx.sharepoint.com/teams/yyyyyyy/”, [ApiVersion = 15])

        1. That worked like a charm… Brilliant. Might just do a Post on that and will give you all the glory you deserve 🙂

  5. Fran Reed says:

    This is great. Love it. Thank you… and replaces a macro template I built for clients importing CSV files. And while its great for us, trying to get them to trust the process…hmm…I don’t think we are quite there yet.

  6. Tom Handschmann says:


    Is there any chance to pull the values from a specific range of equally looking Excel files (all forms) and list them?
    Example: The cell C9 of all forms contains one product characteristic. This characteristic shall be now listed as “Characteristic A”.

    Any help is appreciated.


  7. The answer is quite long but since this is not the first request for this, I’m just gonna write a post. I’ll try to write it today or tomorrow at the latest …

    Read about it here

  8. Laura Rothwell says:

    Does Power Query have the ability to pull monthly files from a folder, to maintain a 12 month rolling report? If so, how would you update the query to add a month/drop a previous month?

    Thank you

  9. The easiest way to do that would be to simply delete the old files, when new arrivals come.

    The harder way would be to go for the (Year*12 + Month) indexing of your files (you can do the calculation on the fly in Power Query, but you need it to uniquely index your months) and then filtering out those that are more that 12 less than the Maximum index…

  10. Laura Rothwell says:

    What if you didn’t want to delete the old files? Is it best to copy the files into a new folder to better manage the 12 month rolling report?

    Thank you for your response.

  11. Dinu says:

    Great post, thank you.

    But i am looking for a way to include a new column with the folder name.

    For example , I have:

    – Year 2016
    – – – January
    – – – February
    … and so on

    unfortunate the data doesn’t contain the Dates, but I need to include it in the Pivot I am making from the imported data for the final report.

    Is there a way to add a column with folder names (like January)

    1. Read this article I wrote
      it takes a slightly different route but in the end you will see that you can get the whole path, and if you can do that, you can also split by delimiter (the right-most) “/” and get the folder name and then you are one M formula away from eternal happiness and that is the Date.FromText… Read more about it here

  12. Lana says:

    Hi Gasper!
    How much data can be imported like this into data model?
    I have a number of unstructured messy files, totalling 350MB.
    as soon as number of rows in data model reaches just over 1m, i get an error about memory 32-64.

    If you have ANY tip, please share, would be most grateful.

    1. This one’s easy, you need(!) 64bit Excel.

      1. Lana says:

        You were right! Worked like magic 😉 but still, how much data can go into 64bit data model?

        1. From this point on it’s no longer an Excel limitation, it’s as much as your computer has free…

  13. Jonas says:

    There is a function get the file modification date?
    When I open the files by paste, I can see the date, but when I open file data, I can not see the dates.

  14. Amadeusz Annissimo says:

    Very helpful, easy to follow guide. Thank you for writing it.

  15. Tilen says:

    I have two xls files in the folder. But when refreshing excel spreadsheet only one appears (first one on the alphabetic order). Why is the second one not automaticly added?

    1. Hi,
      if you follow this post
      it will work. Sorry I can’t be more specific as to why in your case it doesn’t work but there are just to many variables to pinpoint the problem with any degree of certainty.

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: