The site will merge multiple excel format data into one excel every month. Example: Monthly data from January to December now need to be aggregated together for annual data. This is a very common requirement, how to solve it?
Traget
There is a folder containing several Excel documents, and each document has several worksheets. At the same time, the documents in this folder may be increased or decreased at any time. Therefore, we need to find a data that can be applied to a variable number of documents at any time and can capture specific areas in each document. Which method is the most effective? The answer is: Power Query
Steps
The specific operation is divided into three parts, which only takes about 2 minutes in total:
- Create basic queries
- Custom query content
- Back to results
>>> Step 1: Create a new query
First we create a basic query that contains information about each workbook in the folder. The path can be: data->get data->from file->from folder
Select the folder path and click OK.
After loading the query results, click Edit to enter the Power Query editor.
>>> Step 2: Custom query
Use the toolbar tool to add columns. The path is Add Column -> Custom Column. Then enter the following command:=Excel.Workbook(File.Contents([Folder Path]&[Name]))
For our newly added column, we need to expand. Simply expand all of its columns.
At present, we have not got any data information, so we need to further expand. Expand the workbook.data column. Considering that the data information we need are in columns B and C of the original worksheet file. So here only need to expand Column2 and Column3.
After unfolding, we got a table with lots of useless information.
Filter the information of Column2 and only keep Total.
Delete useless columns, leaving only Name, Workbook Name, and Workbook.Data.Column2.
After loading the query results, you get such an effect.
>>> Step 3: Data Update
As mentioned earlier, the results created by the query can be updated at any time. For example, as shown in the figure above, we copy a file3, as long as a simple update, so the data can be directly captured. It really takes 2 minutes to solve the problem of merging multi-document data once and for all.