Search multiple excel files for data plus#
Search multiple excel files for data download#

To turn off the need to double refresh, follow my post on Data Refresh. The Pivot Table refreshed before the data finished loading from the query, so we have to click Data -> Refresh All again to refresh the Pivot Table. “If it’s updated, why hadn’t the Pivot Table updated?” – Good question. Now for the moment of truth… in Excel, click Data -> Refresh All, the data will be updated.

I have created a Pivot Table to show that the January & February files have been combined.Ĭopy the Example 10 – March 2019.csv file into the folder. You can see that both the January and February data has been combined into this single table.Ĭlick Home -> Close & Load to load the data into Excel. Once it has finished, we can see the files have been combined, that’s all it took, one button! As shown in the screenshot below, a lot of transformations and steps have been made to achieve this. The Combine File window will open, showing a sample of the data using the first file in the query as an example. Go ahead and click it… you know you want to. This uses Power Query’s magic built-in combine process.Īssuming we didn’t do Method 1, the Preview window should look like this:ĭid you notice the icon with the two down arrows at the top of the Content column? That’s the Combine Files icon, it drills from the file level into the content level, and combines each file. That’s it all done, click Close & Load to load the Table into Excel. Our preview window should now look like this:

In the Value column filter to remove the text of “Value”.

Add the column headers to be Source File, Date, Customer, Product, Sold By and Value.

We have now expanded all the data from both files, and the January & February files are combined. This is the Expand icon and indicates that the column contains more detail which can be expanded.Ĭlick on the Expand icon, then click OK as we want to keep all the columns of data. You will notice a new icon in the CSV column header. In the Custom Column window, input the following informationĬustom column formula: =Csv.Document()Ī new column called CSV is added to the preview window. We have kept the Name column to illustrate that using this method you can retain file attributes through into the final output. This removes the file attribute columns which we do not require. Start by selecting the Content and Name columns, then click Home -> Remove Columns -> Remove Other Columns. The first method uses a Custom Column containing a Power Query function. 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.

Example 10 – April 2019.xlsx Setting up the files for the examples.

The examples in this post use the following files:

Search multiple excel files for data plus#

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

Search multiple excel files for data download#

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.