Cập nhật nội dung chi tiết về Join Two Or More Tables In Excel With Power Query mới nhất trên website Beiqthatgioi.com. Hy vọng thông tin trong bài viết sẽ đáp ứng được nhu cầu ngoài mong đợi của bạn, chúng tôi sẽ làm việc thường xuyên để cập nhật nội dung mới nhằm giúp bạn nhận được thông tin nhanh chóng và chính xác nhất.
In this tutorial, we will look at how you can join tables in Excel based on one or more common columns by using Power Query and Merge Tables Wizard.
Combining data from multiple tables is one of the most daunting tasks in Excel. If you decide to do it manually, you may spend hours only to find out that you’ve messed up important information. If you are an experienced Excel pro, then you can possibly rely on VLOOKUP and INDEX MATCH formulas. A macro, you believe, could do the job in no time, if only you knew how. The good news for all Excel users – Power Query or Merge Tables Wizard can be your time-saver. The choice is yours.
How to join tables with Excel Power Query
In simple terms, Power Query (also known as Get & Transform in Excel 2016 and Excel 2019) is a tool to combine, clean and transform data from multiple sources into the format you need such as a table, pivot table or pivot chart.
Among other things, Power Query can join 2 tables into 1 or combine data from multiple tables by matching data in columns, which is the focus of this tutorial.
For the results to meet your expectations, please keep in mind the following things:
Power Query is a built-in feature in Excel 2016 and Excel 2019, but it can also be downloaded in Excel 2010 and Excel 2013 and used as an add-in. In earlier versions, some windows may look different from the images in this tutorial that were captured in Excel 2016.
For the tables to be combined correctly, they should have at least one common column (also referred to as a common id or key column or unique identifier). Also, the common columns should contain only unique values, with no repeats.
The source tables can be located on the same sheet or in different worksheets.
Unlike formulas, Power Query does not pull data from one table to another. It creates a new table that combines data from the original tables.
The resulting table does not update automatically. You should explicitly tell Excel to do this. Please see how to refresh a merged table.
Source data
As an example, let’s join 3 tables based on the common columns Order ID and Seller. Please note that our tables have different numbers of rows, and although table 1 has duplicates in the Seller column, table 3 contains only unique entries.
Our task is to map the data in table 1 with the relevant records from the other two tables, and combine all the data into a new table like this:
Table 1 is named Orders
Table 2 is named Products
Table 3 is named Commissions
Create Power Query connections
Not to clutter your workbook with copies of your original tables, we are going to convert them into connections, do the merge within the Power Query Editor, and then load only the resulting table.
To save a table as a connection in Power Query, here’s what you do:
Select your first table (Orders) or any cell in that table.
This will create a connection with the name of your table/range and display that connection in the Queries & Connections pane that appears on the right-hand side of your workbook.
Repeat the above steps for all other tables you want to merge (two more tables, Products and Commissions, in our case).
When finished, you will see all the connections on the pane:
Merge two connections into one table
With the connections in place, let’s see how you can join two tables into one:
In the Merge dialog box, do the following:
Select your 1st table (Orders) from the first drop-down.
Select your 2nd table (Products) from the second drop-down.
In the Join Kind drop-down list, leave the default option: Left Outer (all from first, matching from second).
Upon completion of the above steps, the Power Query Editor will show your first table (Orders) with one additional column named like your second table (Products) added to the end. This additional column does not have any values yet, just the word “Table” in all the cells. But don’t feel discouraged, you did everything right, and we are going to fix that in a moment!
Select the columns to add from the second table
At this point, you have a table resembling the one in the screenshot below. To complete the merging process, perform the following steps within the Power Query Editor:
In the box that opens, do this:
Keep the Expand radio button selected.
Unselect all columns, and then select only the column(s) you want to copy from the second table. In this example, we select only the Product column because our first table already has Seller and Order ID.
Uncheck the Use original column name as prefix box (unless you want the column name to be prefixed with the table name from which this column is taken).
As the result, you will get a new table that contains every record from your first table and the additional column(s) from the second table:
If you need to merge only two tables, you may consider the work almost done and go load the resulting table in Excel.
Merge more tables (optional)
In case you have three or more tables to join, there is some more work for you to do. I will outline the steps briefly here, because you have already done all this when joining the first two tables:
Save the table you’ve got in the previous step (shown in the screenshot above) as a connection:
The screenshot below shows my settings:
In this example, we add only the Commission column:
As the result, you get a merged table that consists of the first table, plus the additional columns copied from the other two tables.
Import the merged table to Excel
With the resulting table in the Power Query Editor, there is just one thing left for you to do – load it in your Excel workbook. And it is the easiest part!
In the Import Data dialog box, select Table and New Worksheet options.
A new table combining the data from two or more sources appears in a new worksheet. Congratulations, you did it!
As a finishing touch, you may want to apply the right number format to some columns and maybe change the default table style to your favorite one. After these improvements, my combined table looks very nice:
In the previous example, we were combining tables by matching data in one key column. But there is nothing that would prevent you from selecting two or more column pairs. Here’s how:
After that, perform exactly the same steps as described above, and your tables will be merged by matching values in all the key columns.
Merge Tables Wizard – quick way to join 2 tables in Excel
Now that you are familiar with the inbuilt tool, let me show you our approach to merging tables in Excel.
In this example, we will be combining the same tables that we joined with Power Query a moment ago. I have just added a few more rows to the second table to show you more capabilities of our add-in:
With the Merge Tables Wizard installed in your Excel, here’s what you need to do:
Please notice the Case-sensitive matching box at the top. Select it if you want to treat uppercase and lowercase text in the key columns as different characters. For this example, we don’t need that, so we leave the box unselected.
We select the Seller column because we have more rows in the second table and we want the new seller names to appear in the existing Seller column:
This step is very important because it determines how your tables will be merged. In this example, we go with the default options shown in the screenshot below. But I’d like to draw your attention to the following 2 boxes that can prevent overwriting your existing data in case you’ve chosen to update some columns:
Empty cells only
Only if cells in the lookup table contain data
With the default options, the wizard highlights the newly added rows and adds the Status column. If you don’t want any of that, clear the corresponding boxes in the last step.
To join three and more tables, simply repeat the above steps. Just remember to select the result of a previous merge as your main table.
This example has shown just one scenario that our wizard can handle, but there is much more to it! If you are curious to know other use cases, please check out these examples.
Also, you can download a a trial version of Ultimate Suite for Excel that includes Merge Tables Wizard as well as 60+ other useful tools.
In case you are looking to join tables in some other way, you may find the following resources useful.
Other ways to combine data in Excel:
Merge tables by column headers – join two or more tables based on column names. You can choose to combine all the columns or only the ones you select.
Combine multiple worksheets into one – copy multiple sheets into one summary worksheet. Of course, it’s not manual copy/pasting! You only indicate which worksheets to merge, and our Copy Sheets tool does the rest.
Compare two Excel files – how to compare two tables (worksheets) for differences and merge them into a single sheet.
How To Combine Tables With Power Query
Bottom Line: Learn how to combine tables in Excel using Power Query.
Skill Level: Intermediate
Video Tutorial
Download the Excel File
If you’d like to download the file that I use in the video, you can do so here:
Here is the file that contains the VBA macro to create Power Query connections to all tables in the workbook.
Combining Tables
If you have tables on several worksheets that contain the same type of data and you are looking to combine them into one master table, Power Query can help you do it quickly and effectively. This is a great alternative to copying and pasting data piece by piece, which can get tedious if there are several tables that you want to merge.
There are just two prerequisites to keep in mind.
Prerequisite #1
All of the sheets or data sets that you are looking to combine must be formatted as Excel Tables, not just data set up in a table format.
To turn a data set into an Excel Table, just select any cell in the set and then choose Format as Table on the Home tab. It’s usually a good idea to name the table after you’ve created/inserted it.
If you are relatively new to Excel Tables, check out my Beginner’s Guide to Excel Tables. Here are some useful Tips & Shortcuts for Inserting Excel Tables, and this post will give you some Best Practices for Naming Excel Tables.
Prerequisite #2
The tables you are working with must contain the same column headings, though they do not have to be in the same order. If you are working with columns that have similar data, but your headings are not the same, Power Query will put them into different columns when it combines them.
There are ways around this, which I will cover in a future post.
The Setup Work in Power Query
Now you can create queries in Power Query. First we will create connection queries for each table. Then we will combine those queries with an Append query to combine or stack the data.
1. Create Connection Queries to the Tables
To combine, or append, your tables together, you need to create a connection to each of them in Power Query.
This brings up a preview of your data. To create a connection:
Select Close & Load To…
That brings up the Import Data window. From here, select Only Create Connection.
You can see the connection you’ve just created in the Queries & Connections pane. If ever you don’t see the Queries & Connections pane, you can open it by selecting that button on the Data tab in the ribbon.
This process of creating connections must be repeated for every table that you want to append. Again, you only need to do this work one time for the initial setup. However, here are a few tips to speed up the process.
Use the Table Connections Macro
Since creating and connecting lots of tables can be time-consuming, I’ve created a macro that automates it. The macro loops through all tables in the workbook and creates connection only queries for any table that do not have queries yet.
I will write a post in the future that explains the macro. However, you can download the file that contains the VBA macro code here.
The macros runs on the Active Workbook. You can add the macro to your Personal Macro Workbook and add a macro button to the Ribbon or Quick Access Toolbar to run it on any open workbook.
Close & Load Settings
If you’d rather not use a macro, you can also shorten the process by changing the setting of the Close & Load split-button. The default for the top half of that button will load the output table to a new sheet, but you can adjust the settings so that it only creates a connection instead. To change the setting:
Go to the File menu.
Select Options and Settings.
Choose Query Options.
That will bring up the Query Options window, where you can select Specify custom default load settings.
Deselect the Load to worksheet option.
Hit OK.
Just remember to change this setting back once you’ve finished connecting all of your tables.
2. Combining Connected Tables with Append
Once all of your tables are connected, it’s a piece of cake to consolidate them:
This brings up the Append window, where we can select Three or more tables. This allows us to move any or all of the tables that we’ve connected from our Available tables (on the left) to the list of Tables to append (on the right).
Once you hit OK, you will be taken back to the Power Query editor, where you can see a preview of the combined tables. You can make adjustments and transformations to the data before closing the editor and loading the data to a new worksheet.
Updating & Refreshing the Data
This means we have fully automated this process. You do NOT have repeat the steps above every time your data changes or you get new rows in your tables.
Adding New Tables
If you ever want to add new tables to the query (or exlcude existing ones) you can reopen the Append window by:
Opening the Query Settings pane if it’s not already visible (View tab, then Query Settings).
This opens the Append window, where you can add or delete tables.
The new columns will still need to have the same column header name on each sheet. If any of the tables are missing columns, then Power Query will fill the rows for that table with blank (null) values in the append query and output table.
Other Power Query Posts
If you’re just getting started with Power Query, check out my overview post here: Power Query Overview: An Introduction to Excel’s Most Powerful Data Tool.
Then get Power Query up and running with this tutorial: The Complete Guide to Installing Power Query.
Free Training Webinar on the Power Tools
Right now I’m running a free training webinar on all of the Power Tools in Excel. This includes Power Query, Power Pivot, Power BI, pivot tables, macros & VBA, and more.
It’s called The Modern Excel Blueprint. During the webinar I explain what these tools are and how they can fit into your workflow.
You will also learn how to become the Excel Hero of your organization, that go-to gal or guy that everyone relies on for Excel help and fun projects.
How To Properly Merge Two Tables In Ms Word
Asked
I would like to merge 2 tables together in Microsoft Word. I have attempted to do the usual drag and drop, but I’m out of luck!
I don’t know if my mouse is the problem, but even if I use the touch pad I can reproduce this issue.
Der Hochstapler
80.1k 55 55 gold badges 248 248 silver badges 296 296 bronze badges
Wern Ancheta Wern Ancheta
6,112 26 26 gold badges 96 96 silver badges 142 142 bronze badges
From allexperts.com:
There are a few things you can check on:
Make sure that the second table doesn’t have any rows marked as heading rows.
Make sure that neither table is wrapped (wrapping should be set to None on the Table tab of Table Properties).
Make sure that neither table (even if they appear identical) is nested in one large cell of a containing table (this sometimes happens with material pasted from the Web).
That said, I can tell you that I once had two tables–which I had created myself, so I know there was nothing unusual about them–that just refused to merge, for no apparent reason. It’s possible that the table structures were somehow damaged, and if I’d been doing this in Word 2003, perhaps using Open and Repair would have fixed the problem. As it was, it wasn’t vital that the tables be actually part of the same table, so I shrugged and moved on.
If you encounter such a situation, you have really only two recourses (if Open and Repair doesn’t help):
Add rows to the first table and copy/paste the content of the second table into them.
Convert both tables to text, then convert all the text back to a single table.
Sorry I can’t be more definitive, but this is a mystery to me, too!
Suzanne S. Barnhill Microsoft MVP (Word) 1998-2006
When nothing else works, try the following solution (works in Word 2010):
Reveal hidden formatting characters (try Ctrl-Shift-8 or Alt,H,8).
Select the lower table’s contents (try Alt,J,L,K,T).
Check the row height.
For Windows 10
Under Tables – Layout
Go to Cell Sizes
Uncheck Specify Height and in “Row Height is …” put at least
Under Options, check “Allow row to break across pages”.
Press Ctrl + Shift + 8. This will show all the non-printing characters in the word document. Delete the paragraph symbol ¶ between the two tables which you want to combine. The two tables will be combined.
If both tables are highlighted, it is likely that the lower table is nested in the upper table.
If only the lower table is highlighted, cut it (Ctrl–X) and paste it in the non-table space below the upper table.
Then proceed with the table merge strategies described by others.
Find one between two tables that prevents them from joining. Put your cursor next to it and press Delete. It will go away, and the tables would stick together.
I went to hell and back with this problem. I tried everything listed here above, and on many other forums and sites, but nothing worked. I was trying to combine two IDENTICAL tables, each copied from a separate documents, to no avail. The tables look merged, but there is always a thicker line between them and each table would still be separate.
How I fixed it in the end was quite amusing yet incredibly unsatisfying:
Make sure you try everything mentioned before (wrapping, style, size…).
Leave the two tables apart.
Save the document.
Exit the document.
Open the document.
Delete the space between the two tables.
The tables merged.
Whatever was messed up with Word needed only a restart of the document to get fixed.
After trying all of these I finally had a brainwave. Added, I am working on office for mac, so it might be a bit different, but try this (I love how simple this ended up being!):
Drag Select the last row of the first table you want to merge together with the first row of the table underneath, go to the Edit tab and select Merge table.
hahaha… so easy, but not really intuitive. Should this be under the table tab, Microsoft? (maybe I’m missing something?).
The merging of table can be done using text wrapping properties.
For that, select first table.
Now select second table. do the same thing as mentioned in point No.2
If there is any gap between the 2 tables, place the cursor in the gap and press delete button or if you cannot place the cursor in between, place the cursor in the last row/ cell in the first table and press delete button.
And now the 2 tables become combined.
Save the document as an older version of Word, i.e. Word 97-2003. This will allow you to merge the two tables simply by deleting the ^p (paragraph/return) symbol.
This worked for me after trying everything above without success.
You can convert the document back to your current version of Word by ‘Save As’.
Highly active question. Earn 10 reputation in order to answer this question. The reputation requirement helps protect this question from spam and non-answer activity.
Super User works best with JavaScript enabled
Accept all cookiesCustomize settings
Tạo Các Công Thức Power Query Trong Excel
Tạo một công thức đơn giản
Để xem ví dụ về công thức đơn giản, chúng ta hãy chuyển đổi một giá trị văn bản thành kiểu chữ thích hợp bằng cách dùng công thức Text.Proper() .
Trong thanh công thức Trình soạn thảo Truy vấn, hãy nhập = Text.Proper(“text value”), và nhấn Enter hoặc chọn biểu tượng Enter.
Power Query cho bạn thấy kết quả trong ngăn kết quả công thức.
Để xem kết quả trong trang tính Excel, hãy chọn Đóng & Tải.
Kết quả sẽ trông như thế này trong một trang tính:
Bạn cũng có thể tạo công thức truy vấn nâng cao trong Trình soạn thảo Truy vấn.
Tạo công thức nâng cao
Để xem ví dụ về công thức nâng cao, chúng ta hãy chuyển đổi văn bản trong một cột thành kiểu chữ thích hợp bằng cách kết hợp nhiều công thức. Bạn có thể sử dụng Ngôn ngữ Công thức Power Query để kết hợp nhiều công thức thành các bước truy vấn có kết quả tập dữ liệu. Có thể nhập kết quả vào một trang tính Excel.
Chẳng hạn, giả sử bạn có một bảng Excel chứa các tên sản phẩm mà bạn muốn chuyển thành kiểu chữ thích hợp.
Bảng ban đầu trông như thế này:
Và bạn muốn bảng kết quả trông giống như thế này:
Chúng ta hãy xem qua các bước công thức truy vấn để thay đổi bảng ban đầu sao cho các giá trị trong cột Tên Sản phẩm có kiểu chữ thích hợp.
Ví dụ về truy vấn nâng cao bằng Trình soạn thảo Nâng cao
Tạo một chuỗi các bước công thức truy vấn bắt đầu bằng câu lệnh let. Vui lòng lưu ý rằng Ngôn ngữ Công thức Power Query có phân biệt chữ hoa, chữ thường.
Mỗi bước công thức truy vấn xây dựng trên bước trước đó bằng cách tham chiếu đến một bước theo tên.
Tạo đầu ra cho công thức truy vấn bằng câu lệnh in. Thông thường, bước truy vấn sau cùng được dùng làm kết quả tập dữ liệu cuối cùng.
Bước 1 – Mở Trình soạn thảo Nâng cao
Trong Trình soạn thảo Truy vấn, hãy chọn Trình soạn thảo Nâng cao.
Bạn sẽ thấy Trình soạn thảo Nâng cao.
Bước 2 – Xác định nguồn ban đầu
Trong Trình soạn thảo Nâng cao:
Dùng câu lệnh let gán Source = công thức Excel.CurrentWorkbook(). Thao tác này sẽ sử dụng bảng Excel làm nguồn dữ liệu. Để biết thêm thông tin về công thức Excel.CurrentWorkbook(), hãy xem Excel.CurrentWorkbook.
Gán Source cho kết quả in.
letSource = Excel.CurrentWorkbook(){[Name="Orders"]}[Content] inSource
Truy vấn nâng cao của bạn sẽ trông giống như thế này trong Trình soạn thảo Nâng cao.
Để xem kết quả trong một trang tính:
Bấm Xong.
Trong ribbon Trình soạn thảo Truy vấn, bấm Đóng & Tải.
Kết quả sẽ trông như thế này trong một trang tính:
Bước 3 – Tăng cấp hàng đầu tiên thành tiêu đề
Để chuyển đổi các giá trị trong cột Tên Sản phẩm thành kiểu chữ thích hợp, trước tiên bạn cần tăng cấp hàng đầu tiên thành tiêu đề cột. Bạn thực hiện điều này trong Trình soạn thảo Nâng cao:
Thêm #”First Row as Header” = công thức Table.PromoteHeaders() vào các bước công thức truy vấn của bạn và tham chiếu đến Source là nguồn dữ liệu. Để biết thêm thông tin về công thức Table.PromoteHeaders(), hãy xem Table.PromoteHeaders.
Gán #”First Row as Header” cho kết quả in.
let Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content], #"First Row as Header" = Table.PromoteHeaders(Source) in #"First Row as Header"Kết quả sẽ trông như thế này trong một trang tính:
Bước 4 – Thay đổi mỗi giá trị trong một cột thành kiểu chữ thích hợp
Để chuyển đổi mỗi giá trị trong cột Tên Sản phẩm thành kiểu chữ thích hợp, bạn dùng Table.TransformColumns() và tham chiếu đến bước công thức truy vấn “First Row as Header”. Bạn thực hiện điều này trong Trình soạn thảo Nâng cao:
Thêm #”Capitalized Each Word” = công thức Table.TransformColumns() vào các bước công thức truy vấn của bạn và tham chiếu đến #”First Row as Header” là nguồn dữ liệu. Để biết thêm thông tin về công thức Table.TransformColumns(), hãy xem Table.TransformColumns.
Gán #”Capitalized Each Word” cho kết quả in.
let Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content], #"First Row as Header" = Table.PromoteHeaders(Source),#"Capitalized Each Word" = Table.TransformColumns(#"First Row as Header",{{"ProductName", Text.Proper}}) in#"Capitalized Each Word"Kết quả cuối cùng sẽ thay đổi mỗi giá trị trong cột Tên Sản phẩm thành kiểu chữ thích hợp và trông giống như thế này trong trang tính:
Với Ngôn ngữ Công thức Power Query bạn có thể tạo các truy vấn dữ liệu từ đơn giản đến nâng cao để khám phá, kết hợp và tinh chỉnh dữ liệu. Để tìm hiểu thêm về Power Query, hãy xem Trợ giúp Microsoft Power Query cho Excel.
Bạn đang đọc nội dung bài viết Join Two Or More Tables In Excel With Power Query trên website Beiqthatgioi.com. Hy vọng một phần nào đó những thông tin mà chúng tôi đã cung cấp là rất hữu ích với bạn. Nếu nội dung bài viết hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất. Chúc bạn một ngày tốt lành!