Which method you choose is up to you, and you may even decide to use different methods for different circumstances. It is always good practice to filter to only include the files we need you never know when another user might decide to save a random file within the folder.įilter the Extension column to include only. Our first transformation will be to keep only the files we wish to be combined (the CSV files for our current example). It does not show the data from the file as it has in the past, instead, it shows data about the files. Instead, we will click Transform Data and take a closer look at what’s happening here. xlsx files in the folder we can’t combine these easily.

If the files are entirely uniform and there are no edits to make, we could just click Combine (drop-down button) -> Combine Close & Load.

Click Browse…, navigate to the folder to be used, then click OK.Ī preview of the folder and file attributes is displayed. In Excel, click Data -> Get Data -> From File -> From Folder The first row is treated as the header row.The easiest files to work with are CSV or text files, so that’s an excellent place to start. These are the files we will import initially.ĭo not include the March or April files, we will add them in later as part of the examples. To work along with the examples in this post, start by moving the January and February files into a separate folder. The examples in this post use the following files: Then you’ll be able to work along with examples and see the solution in action, plus the file will be useful for future reference.ĭownload the file: Power Query – Example Files I recommend you download the example file for this post. Power Query is magic, but you’ve got to give it a reasonable chance. All the files to be imported must follow a similar structure and column pattern. This can save you hours and hours.īefore we get started on this technique, there is one point which I need to make you aware of. That’s powerful stuff, right! If we want to add another file into the output table, we only have to save a copy of the file in the folder and click refresh, the new file will be imported too. We give Power Query a folder path, click a few buttons and it will import and combine all the files into a single table. In this post, we are looking at using Power Query to import all the files in a folder.

Get and transform excel 2013