Home > Topics > excel > body text

Excel Case Sharing: Using Power Query to Merge Multiple Tables

青灯夜游
Release: 2022-09-16 10:07:41
forward
8939 people have browsed it

In the previous article "Practical Excel Skills Sharing: Four Methods for Summing across Worksheets", we learned four methods for summing across worksheets. Today we are going to get to know the Power Query plug-in in excel. It turns out that summarizing worksheets is so convenient and fast!

Excel Case Sharing: Using Power Query to Merge Multiple Tables

At the end of each month, the Finance King will summarize the sales data from various regions across the country for data analysis. The method he used before was to copy and paste the data from each worksheet into one worksheet, and then perform pivot table analysis. But there are so many cities in the country, copying and pasting is too time-consuming, and what if the intermediate data changes? Don’t worry now. Today I will introduce to you a very useful skill in EXCEL: powerquery multi-table merging, which helps you dynamically obtain data from multiple worksheets.

1. First introduction to Power Query

As shown below, in this workbook, each worksheet lists the sales in each region of the country. Data, for the sake of example, only the basic data of four cities are listed here.

Excel Case Sharing: Using Power Query to Merge Multiple Tables

First, open the data tab, in the [Get and Transform] group, create a new query---from file---from workbook.

Excel Case Sharing: Using Power Query to Merge Multiple Tables

Power Query is originally a plug-in for EXCEL. In previous versions of EXCEL2016, it needs to be downloaded separately. However, in Excel2016, this skill has been built into the data tab [Get and Convert] group for direct use by everyone. This is enough to show the importance of Power Query. It is recommended that you upgrade to the latest EXCEL version. For versions before EXCEL2016, you can download and install the powerquery plug-in from Baidu.

Excel Case Sharing: Using Power Query to Merge Multiple Tables

Find the workbook in the pop-up window and import it.

Excel Case Sharing: Using Power Query to Merge Multiple Tables

In the pop-up navigator interface, check "Select Multiple", select all the worksheets that need to be merged, and then select "Edit" in the lower right corner .

Excel Case Sharing: Using Power Query to Merge Multiple Tables

This will enter the POWER QUERY editor interface.

Excel Case Sharing: Using Power Query to Merge Multiple Tables

This interface is the main interface for us to perform Power Query operations. You can see that the menu bar above is very similar to the EXCEL menu bar. The query window on the left displays For the four open worksheets, the query setting interface on the right is similar to PS and can record and return operations. The middle area displays the contents of the table.

2. Use Power Query to summarize data

Click on the Append Query in the [Combined] group under the Home tab.

Excel Case Sharing: Using Power Query to Merge Multiple Tables

#Select "Append query as new query" in the drop-down list.

Excel Case Sharing: Using Power Query to Merge Multiple Tables

Since there are multiple tables here, choose to append three or more tables. Add the available tables on the left to the right and click OK

Excel Case Sharing: Using Power Query to Merge Multiple Tables

We see that there is an additional query table "Append1" in the query window on the left. This table is summarized All the data in the four tables, and the middle table area displays the contents of all tables merged.

Excel Case Sharing: Using Power Query to Merge Multiple Tables

What we need to do now is to return the merged data to the worksheet. Here, select Close and Upload in the [Close] group under the Home tab, and check Select "Close and upload to".

1Excel Case Sharing: Using Power Query to Merge Multiple Tables

In the "Load to" window, select "Only create a connection" and click "Load".

1Excel Case Sharing: Using Power Query to Merge Multiple Tables

The worksheet query window will appear on the right side of the workbook. Shown are the five tables in the query window in the Power Query editor.

Excel Case Sharing: Using Power Query to Merge Multiple Tables

What we have to do is to display the new query table "Append1" into the table. Select "Append1", right-click and load to

1Excel Case Sharing: Using Power Query to Merge Multiple Tables

Select "Table" in the "Load to" window, select "New Worksheet" for the location to upload the data, and click "Load".

1Excel Case Sharing: Using Power Query to Merge Multiple Tables

The merged data will be displayed in the new worksheet. Let's rename this worksheet "Merge". The data is shown below.

1Excel Case Sharing: Using Power Query to Merge Multiple Tables

3. Update data

What if the data changes?

For example, in the consolidated table just now, we can see that the total sales volume in the consolidated data is 102,281. The first piece of data, Li Liu’s sales in Beijing on July 1, 2018, is 1,780.

Excel Case Sharing: Using Power Query to Merge Multiple Tables

Now we try to modify Li Liu’s sales in Beijing on July 1, 2018 to 2500, click Save.

1Excel Case Sharing: Using Power Query to Merge Multiple Tables

#Then click Refresh All in the [Connection] group under the Data tab.

Excel Case Sharing: Using Power Query to Merge Multiple Tables

We can see that the data changes immediately.

Excel Case Sharing: Using Power Query to Merge Multiple Tables

This is the most powerful thing about Power Query. Whenever you modify the data or even add data or reduce data, you can directly modify it in the data source table, then save it, and finally pass "Refresh All" synchronizes data with one click, as long as the location and name of our data source have not changed.

How about it? Is Power Query very convenient? If you like it, you can try it yourself.

Related learning recommendations: excel tutorial

The above is the detailed content of Excel Case Sharing: Using Power Query to Merge Multiple Tables. For more information, please follow other related articles on the PHP Chinese website!

Related labels:
source:itblw.com
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template