Home >Topics >excel >Practical Excel skills sharing: Use Power Query to merge workbooks in folders

Practical Excel skills sharing: Use Power Query to merge workbooks in folders

青灯夜游
青灯夜游forward
2022-11-14 19:54:563496browse

Practical Excel skills sharing: Use Power Query to merge workbooks in folders

I previously introduced you to the use of EXCEL’s new function Power Query to summarize the worksheets in the workbook, but the function of Power Query is far more than that. Today I will introduce it to you. More advanced merging techniques: Use Power Query to merge workbooks in folders.

As shown below, there are sales data for four regions in the "Sales" folder on the desktop. The title names in each workbook are consistent, and the order can be different. The value of the city column in each workbook is the name of the workbook, which is convenient for viewing the merge effect later.

Practical Excel skills sharing: Use Power Query to merge workbooks in folders

Power Query

#The operation is as follows:

Close the files in the folder, create a new workbook, click on the Data tab, [Get and Transform] group "New Query" --- "From File" --- "From Folder".

Practical Excel skills sharing: Use Power Query to merge workbooks in folders

Enter the folder path, or browse to select the folder location and click OK.

Practical Excel skills sharing: Use Power Query to merge workbooks in folders

This interface lists all the workbooks in the folder, click Edit.

Practical Excel skills sharing: Use Power Query to merge workbooks in folders

Entered the Power Query editor interface. The top is the menu bar, the middle is the table area and the data that will be returned to the worksheet at the end. The query setting window on the right displays the Power Query operation record.

Practical Excel skills sharing: Use Power Query to merge workbooks in folders

As introduced before, the green font in the "Content" column means that this cell contains a file. Click on a cell to preview its contents. (

Note: When previewing the content in the cell, you should place the mouse on the blank space in the cell, not on the text. Clicking on the text will directly open the file in the cell) Since the file is directly opened from the folder The extracted files are all in binary format, so the workbook in binary format appears in the preview pane below.

Practical Excel skills sharing: Use Power Query to merge workbooks in folders

Binary as a binary file cannot be displayed in the table, so what we have to do is to change it into table format and then expand the data into the table inside. Delete other unnecessary columns first.

Select the "Content" column, click "Delete Column" - "Delete Other Columns" in the [Manage Columns] group under the Home tab.

Practical Excel skills sharing: Use Power Query to merge workbooks in folders

In this way, all other columns except the "Content" column are deleted.

Practical Excel skills sharing: Use Power Query to merge workbooks in folders

#So how to convert the binary file into an ordinary table requires the use of Power Query's special programming language-M language. Here I will introduce you to a commonly used function.

Click "Custom Column" in the [General] group under the Add Column option.

Practical Excel skills sharing: Use Power Query to merge workbooks in folders

In the Custom Column window, enter =Excel.Workbook([Content],true) in the "Custom Column Formula", where "[ Content]" You can click "Content" in the available column on the right, and then click on the lower right corner to insert (Note: the capitalization of the formula must not be wrong).

Practical Excel skills sharing: Use Power Query to merge workbooks in folders

Formula analysis:

Excel.Workbook

Function: Return the records of the worksheet from the Excel workbook.

Parameters: Excel.Workbook(workbook as binary, optional useHeaders as nullable logical, optionaldelayTypes as nullable logical) as table

This function returns a table. The first parameter workbook is in binary format, and the second parameter is the logical value of the optional parameter. true means using the title of the original table as the new table. The title, the default is false, means replacing the title of the original worksheet with the new column name. Don't worry about the third parameter.

Here we still use the original title of the form, so fill in true. This saves the subsequent step of improving the first line of the title.

The new column is added successfully. Preview one of the cells. What is shown below is a table-style workbook. This can be directly extended to the table.

Practical Excel skills sharing: Use Power Query to merge workbooks in folders

The "Data" column displays a table in Table format, including the data in the table. Here we only need to extract this column. . Click the expand button in the upper right corner of the custom column, select the expanded column "Data", and uncheck "Use original column name as prefix".

Practical Excel skills sharing: Use Power Query to merge workbooks in folders

The column name becomes "Data". At this time, we preview the data in "Data", and what appears below is the original data in the table. Then extract all the data below.

Practical Excel skills sharing: Use Power Query to merge workbooks in folders

Similarly click the expand button on the upper right side of the custom column, select expand all columns, and do not check "Use original column name as prefix".

Practical Excel skills sharing: Use Power Query to merge workbooks in folders

In this way, we obtain the data in the worksheet by drilling layer by layer.

Practical Excel skills sharing: Use Power Query to merge workbooks in folders

Finally delete the "Content" column. Select the "Content" column and right-click to delete it.

Practical Excel skills sharing: Use Power Query to merge workbooks in folders

#Finally, just upload this table to the form.

Click "Close and Upload" in the [Close] group under the Home tab.

Practical Excel skills sharing: Use Power Query to merge workbooks in folders

#The data will be summarized in the worksheet.

Practical Excel skills sharing: Use Power Query to merge workbooks in folders

When you click the filter button in the "City" column, you will see that the data in the four workbooks are all in the table.

Practical Excel skills sharing: Use Power Query to merge workbooks in folders

What happens when there is one more workbook in the folder? Try placing a new workbook "Xi'an" in this folder.

Practical Excel skills sharing: Use Power Query to merge workbooks in folders

Go back to the table where you just made statistics, click [ under the data tab "Refresh All" in the Connection] group.

Practical Excel skills sharing: Use Power Query to merge workbooks in folders

The city column has "Xi'an" added, which represents the data of this new workbook was added.

Practical Excel skills sharing: Use Power Query to merge workbooks in folders

Summary: Power Query merges folders. As long as the titles in each worksheet are the same, it can be merged and summarized. This method does not care about the folders. Any number of workbooks can be merged. And any data changes can be updated with one click through all refresh.

Power Query is a powerful tool for EXCEL data analysis. Through simple graphical operations, combined with its own M language and through the operation recorder, it helps us to operate more data in a unified manner and quickly complete data processing and optimization. Moreover, it is faster to get started and easier to operate than VBA, and graphical operation can meet most of our needs. Everyone, hurry up and learn!

Related learning recommendations: excel tutorial

The above is the detailed content of Practical Excel skills sharing: Use Power Query to merge workbooks in folders. For more information, please follow other related articles on the PHP Chinese website!

Statement:
This article is reproduced at:itblw.com. If there is any infringement, please contact admin@php.cn delete