Often we are in a situation where we would have to compile or append a huge list of data files together before we get into our analysis. However doing this task manually is a cumbersome and unproductive activity. Earlier advanced users of excel may have used VBA to do this task but now even a beginner can automate this task with a few simple steps. And all of this is possible through Power Query.
I have covered Power Query related topics previously as well, but today I want to specifically address the problem of compiling a list of excel data files from a folder into a single excel file. Also we take this one step further & make folder location dynamic so that you can make this a standalone tool for compiling any datasets. Let's see how it's done!
Steps to append all excel data in a folder into a single excel file
Open an excel file where you want to append the data. Microsoft introduced Power Query in Excel 2010 but it was available as a free to download add-in. However it became a standard excel feature from version 2016, so it is preferred that you use excel 2016 or above.
Go to the Data tab & click on “Get Data” and then select “From File”. Here you will see an option for “Folder”. Select the same, since we need to collate all data from a folder.
You will be prompted to select a folder from the Browse window. Select the folder where you have kept all the excel files
After selecting the folder, Power Query will analyze the data and show you a dialogue box. Now here you have couple of options
Combine & Transform Data - In case there are any data processing required then you can use this option
Combine & Load - Simply append the data & load it into Excel sheet
Combine & Load To - In case you don't want the data to be loaded in excel sheet & instead it will get loaded as a connection. It is a great tool if the data size is huge and you want to bypass the excel sheet limitation of 1 million records.
However, to make things simple for now, I am going to select the Load option since my data is small and I don't have to use this data for further processing.
Now Excel will prompt you to select the Sheet where the data exists. Please note that all files should be uniform & standard i.e.
Column Count & Headers should be the same
Data should be in the same sheet across all files
In this case all my data exist in Sheet 1 of each file, so I’m going to select the same & press OK
And that is it, all your data is compiled or appended in a single sheet. However there is still a burning question in mind.
What happens when I share this Power Query Data Compiler to my colleague?
Simple answer: it will not work out of the box, because Power Query will still be searching the folder path given from my computer.
So how can we make this dynamic, so that anyone can use this as a standard data compiler tool?
Enter a folder location in any of the blank cells. I would recommend to enter the location in a separate sheet than the one which is being used to load the data
Give the cell a name for referring to it later on. I’m going to name it “DataLocation”
Go to Power Query Editor either by
a. Selecting the table loaded & clicking on “Edit” in the Query Tools tab
b. OR Go to Data >> Queries & Connections >> Double Click Final Appended Table
This will take you to the Power Query Editor. Now you need to the “Advanced Editor”, so that we can enter the code to make the folder location dynamic
In the Advanced Editor replace the code for Source with the below code. If you have used any other cell reference name other than “DataLocation” then use that instead.
Source = Folder.Files(Excel.CurrentWorkbook(){[Name="DataLocation"]}[Content]{0}[Column1]),
Now click on “Done” and then click on Close & Load from the Home tab in the Power Query Editor
Comentários