Confronted with numerous sources of data, data experts need to identify the relationships between different pieces of information. To do this, they can merge two tables with Power Query. This query editor simplifies the task of data modeling, for more efficient and reliable analysis.
What is the merge function in Power Query?
Merging tables in Power Query allows you to join two queries found in Excel, or an Excel query and a query from an external data source (such as Power BI).
You can choose between two types of merge:
- Embedded merge: data is integrated into an existing query.
- Intermediate merge: a new query is created for each merge operation.
For example, this type of grouping can be useful for companies with several subsidiaries. In this case, each subsidiary often has its own table representing sales. If you want a consolidated view of all group transactions, you can use Power Query to merge the subsidiary tables.
Good to know: you can only merge two queries for which you have the necessary access rights. Power Query‘s different levels of confidentiality (public, organizational and private) prevent you from accidentally merging tables containing sensitive data.
How do I merge 2 tables with Power Query?
Merging 2 tables with the Power Query editor
If you have 2 tables to group, with at least one or more columns to combine, you can use Power Query to merge the 2 tables. Here’s how to do it:
- Start by opening a query in the Power Query editor.
- Choose a cell and select “Query”, then “Modify”.
- In “Home”, click on “Merge queries”. By default, this is an embedded merge. If you require an intermediate merge, click on “Merge queries as New”.
- In the dialog box, select the main table first, then a join column (simply click on the column header).
- Next, select the associated secondary table, then the corresponding column. You will then see the number of matches.
- This will tell you whether or not there are any errors in the merge operation.
- Select the type of join.
- Click on “Use approximate match to perform the merge”, then choose from the various approximate match options. This step is optional.
- Click on “Include matching lines only”.
- Otherwise, you’ll get a left outer join.
- Click on OK.
The different types of join
Power Query offers several types of joins in its table merge query. Here are your options:
- Internal join: this is the default option. In this case, only the rows corresponding to the primary and secondary tables will be integrated.
- Left outer join: all rows of the primary table are maintained, plus the corresponding rows of the secondary table.
- Right outer join: conversely, all rows from the secondary table are maintained, along with the corresponding rows from the primary table.
- Full outer: rows from two tables are inserted in their entirety.
- Left anti-join: only rows from the primary table that have no correspondence with the secondary table are joined.
- Right anti-join: only rows from the secondary table that do not match the primary table are combined.
- Cross-join: combines all rows in the primary table with those in the secondary table.
Bonus tips
Tip no. 1: To successfully merge 2 tables with Power Query, you must have the same number of columns to match, and above all, select them in the right order.
For example, if, for the main table, you select a unit price column first, a product column second and a quantity column third, you must respect exactly the same order for the secondary table. It is this selection order that will enable the comparison of common columns.
Similarly, linked columns must have the same format. For example, the 2 price columns must contain numbers, while the two product columns contain textual data.
Tip 2: To better understand the relationships between the two merged tables, you can use the “Query dependencies” dialog box. To do this, follow the steps below:
- Click on “Show dependencies”.
- Click on “Query”.
- Select the “Layout” command (this will allow you to control the orientation of the visual representation).
Join DataScientest to manage your databases
Thanks to its wide range of queries, the Power Query editor is one of the preferred tools of data experts for managing and modeling their databases. But mastering all the available queries requires both theoretical and practical Power Query training.
This is exactly what we offer at DataScientest. Once you’ve familiarized yourself with the main concepts, you’ll be able to apply them to real-life situations. You’ll be up and running by the end of the course. Come and join us!
