Cập nhật nội dung chi tiết về How To Merge Tables 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.
Bottom Line: Learn how to merge tables or queries in Power Query to look up data and return matching results. This is similar to a Vlookup or Join where a relationship is created between two tables.
Skill Level: Intermediate
Video Tutorial
Download the Excel File
You can practice merging tables using the same Excel file that I use in the video. Download it here:
Overview
We received a great question from a member of the Excel Campus community, Bill Evans, who wanted to know how to take data from two tables that are formatted differently and combine them into a single sheet using Power Query.
The answer involves using the Merge (or join) feature in Power Query. It basically creates a relationship between two tables to look up data and return matching results.
This is similar to what a VLOOKUP can accomplish with a formula. However, Power Query allows us to automate this entire process, along with any other data cleanup work, and is less prone to formula errors.
If you are looking to combine data by stacking tables together, that is called an Append. You can learn how to append tables in this post: How to Combine Tables with Power Query.
If all of this is sounding a little over your head because you are somewhat new to Power Query, take a break from this post and head over to my Power Query Overview. That will give you a better understanding of how and why it’s used. And this tutorial will walk you through installing Power Query.
Step 1: Create a Connection to the Lookup Table
To join two tables, we want to start by creating a connection-only query for the table that we will be looking up. Usually, when a query is run, it outputs the result in a new table in the workbook. But for this step, we just want to create the connection without creating a new output table. Here’s how:
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.
Step 2: Use the Merge Feature to Join the Tables
Once we’ve established a connection for the lookup table, we can merge it with the data from another table. This other table does not have to be in the same workbook. It could be from another workbook, a CSV file, a webpage, a database, or some other source.
In this example we will use a Table in Excel as the source.
To create a query for that source, start by going to the Data (or Power Query) tab and selecting From Table/Range.
On the Home tab of the Ribbon, select Merge Queries. This brings up the Merge window.
First, in the top part, you can select the column that you want to use for merging.
Then, in the middle, you select the table that you want to merge your data into.
Finally, in the lower section, you will choose the matching column. For my example the columns that we are using to merge both contain the customer ID numbers.
You can leave the Join Kind field as Left Outer. The Left Outer join will return all of the rows from the first table, and only the matching rows from the second table.
At the bottom of the window you’ll see the numbers of rows that were matched. In this case it says “The selection matches 221 of 306 rows from the first table.” This means that some rows from the orders table did not have a matching ID in the customers table. It’s ok for now and we’ll look at how to fix it below.
We can go ahead and press OK.
You may notice that some of the tables have rows that say “null” and when you close and load your query, those cells are blank. This is because when you merged the two tables, Power Query was unable to find some of the data in the source table.
You’ll notice that all of the new columns have headers that begin with the name of the table it came from. That can get a little annoying, so if you want to avoid that, just uncheck the box that says Use original column name as prefix.
Updating the Data
In order to fix the null entries, you can just add the appropriate rows to the lookup table, and then refresh the query.
Going forward, if you make any additions or deletions to the source table(s), a simple refresh of the query will instantly update the output table.
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.
Using this process in this post, two tables that have different column headers are joined together. This is not a VLOOKUP, but it accomplishes the same thing as a VLOOKUP using Power Query instead.
With Power Query we are able to automate the entire data import and cleanup process, which can save you a ton of time and help reduce errors.
Conditional Merge In Power Query
In this example I will use the WideWorldImportersDW sample database Microsoft offers. You should have a copy of SQL Server installed to play with for Power BI. The Developer version of SQL Server is 100% functional and free forever. You just cannot use it in production.
I am going to use two tables for this example, Fact.Sales and Dim.Customer. I only want my Fact Sales table to have customer info for the Buying Group Tailspin Toys. That info is in the Dim Customer table. I can do this several ways.
Do the merge, expand the Buying Group column from the Customer table, then filter to only show those rows.
Pre-filter the Customer table for Tailspin Toys in the Buying Group column, then do the merge. It would need to be an Inner Join, otherwise you will get nulls in the Sales table when you expand, and then you have to filter those out. Which you can do, but it is more steps.
You can bypass all of that and do it in one step.
I’m not going to bore you with how to do methods 1 and 2. Let’s do method 3, a conditional join!
First, create the merge as you normally would. In the Sales table, select Merge Queries from the ribbon, then select the Customer table. Select the Customer Key fields in both. Also make sure this is an inner join so it will remove all records from the Sale Table that are not Tailspin Toys. Your Merge dialog box will look like this:
Table.NestedJoin has the following parameters per MS documentation:
We want to tweak 3rd parameter – table2, which is our Customer table, We don’t want the full table2, we only want table2, filtered for Topspin Toys in the Buying Group field.
So in the code above, we need to replace the 3rd parameter which is just a reference to #”Dimension Customer” – the name of the Customer Table. (It is just Dimension Customer – the #”name here” syntax is just how Power Query references objects with spaces and special characters.)
We want to replace that with this code:
Table.SelectRows(#”Dimension Customer”, each [Buying Group] = “Tailspin Toys”)
Since 100% of the work is done on the SQL Server, it will be very fast compared to how it would run if the Power Query mashup engine on your PC or On-Premise Gateway would process it.
If you are using some other source, like text files or Excel files, folding won’t happen of course, but it should still perform well as this is single filter applied, not a filter applied for every row of either table.
I cannot say that this method is any faster than doing it the longer ways (methods #1 and #2 above) but it is shorter code. Plus, you can get fancier with the Table.SelectRows() function we used by filtering on multiple fields using and/or criteria. You may break folding if you get too fancy and the Power Query engine cannot figure the SQL out, so be careful. If performance is a must, methods #1 or #2 are more likely to fold with many filters in the condition, but you won’t know until you try. Happy Querying!
Join Two Or More Tables In Excel With Power Query
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 Create A Microsoft Query In Excel (Excel Query)
Microsoft Query allows you use SQL directly in Microsoft Excel, treating Sheets as tables against which you can run Select statements with JOINs, UNIONs and more. Often Microsoft Query statements will be more efficient than Excel formulas or a VBA Macro. A Microsoft Query (aka MS Query, aka Excel Query) is in fact an SQL SELECT Statement. Excel as well as Access use Windows ACE.OLEDB or JET.OLEDB providers to run queries. Its an incredible often untapped tool underestimated by many users!
What can I do with MS Query?
Excel Files – you can extract data from External Excel files as well as run a SELECT query on your current Workbook
Access – you can extract data from Access Database files
MS SQL Server – you can extract data from Microsoft SQL Server Tables
CSV and Text – you can upload CSV or tabular Text files
Step by Step – Microsoft Query in Excel
In this step by step tutorial I will show you how to create an Microsoft Query to extract data from either you current Workbook or an external Excel file.
Open the MS Query (from Other Sources) wizard
Select the Data Source
Select Excel Source File
Select Columns for your MS Query
Return Query or Edit Query
Return Data to Microsoft Excel – this will return your query results to Excel and complete the Wizard
View data or edit query in Microsoft Query – this will open the Microsoft Query window and allow you to modify you Microsoft Query
Optional: Edit Query
Import Data
AS you can see there are quite a lot of steps needed to achieve something potentially pretty simple. Hence there are a couple of alternatives thanks to the power of VBA Macro….
MS Query – Create with VBA
Sub ExecuteSQL() Attribute ExecuteSQL.VB_ProcData.VB_Invoke_Func = "Sn14" 'AnalystCave.com On Error GoTo ErrorHandl Dim SQL As String, sConn As String, qt As QueryTable SQL = InputBox("Provide your SQL Query", "Run SQL Query") If SQL = vbNullString Then Exit Sub sConn = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;;Password=;User ID=Admin;Data Source=" & _ chúng tôi & "/" & chúng tôi & ";" & _ "Mode=Share Deny Write;Extended Properties=""Excel 12.0 Xml;HDR=YES"";" Set qt = ActiveCell.Worksheet.QueryTables.Add(Connection:=sConn, Destination:=ActiveCell) With qt .CommandType = xlCmdSql .CommandText = SQL .Name = Int((1000000000 - 1 + 1) * Rnd + 1) .RefreshStyle = xlOverwriteCells .Refresh BackgroundQuery:=False End With Exit Sub ErrorHandl: MsgBox "Error: " & Err.Description: Err.Clear End SubJust create a New VBA Module and paste the code above. You can run it hitting the CTRL+ SHIFT+ S Keyboardshortcut or Add the Macro to your Quick Access Toolbar.
Learning SQL with Excel
Creating MS Queries is one thing, but you need to have a pretty good grasp of the SQL language to be able to use it’s true potential. I recommend using a simple Excel database (like Northwind) and practicing various queries with JOINs.
Alternatives in Excel – Power Query
MS Query vs Power Query Conclusions
, however, it doesn’t entirely invalidate Microsoft Queries. What is more, sometimes using Microsoft Queries is quicker and more convenient and here is why:
You can’t re-run Power Queries without the AddIn. While this obviously will be a less valid statement probably in a couple of years (in newer Excel versions), currently if you don’t have the AddIn you won’t be able to edit or re-run Queries created in Power Query
MS Query Cons: Microsoft Query falls short of the Power Query AddIn in some other aspects however:
Power Query has a more convenient user interface. While Power Queries are relatively easy to create, the MS Query Wizard is like a website from the 90’s
Power Query stacks operations on top of each other allowing more convenient changes. While an MS Query works or just doesn’t compile, the Power Query stacks each transform operation providing visibility into your Data Transformation task, and making it easier to add / remove operations
Bạn đang đọc nội dung bài viết How To Merge Tables 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!