Excel Case Sharing: Using Power Query to Merge Multiple Tables

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!

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.

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

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.

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

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 .

This will enter the POWER QUERY editor interface.

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.

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

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

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.

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".

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

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.

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

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

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

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.

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

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

We can see that the data changes immediately.

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!