Training
Module
Write multi-table queries by using Kusto Query Language - Training
Learn how to write Kusto Query Language (KQL) queries to combine and retrieve data from two or more tables by using the `lookup`, `join`, and `union` operators.
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
A merge queries operation joins two existing tables together based on matching values from one or multiple columns. You can choose to use different types of joins, depending on the output you want.
You can find the Merge queries command on the Home tab, in the Combine group. From the drop-down menu, there are two options:
The merge operation requires two tables:
Note
The position—left or right—of the tables becomes very important when you select the correct join kind to use.
After you select both the left and right tables, you can select the columns that drive the join between the tables. In the following example, there are two tables:
Screenshot of the merge dialog with the Left table for merge set to Sales and the CountryID column selected, and the Right table for merge set to Countries and the CountryID column selected.
The goal is to join these tables by using the CountryID column from both tables, so you select the CountryID column from each table. After you make the selections, a message appears with an estimated number of matches at the bottom of the dialog box.
Note
Although this example shows the same column header for both tables, this isn't a requirement for the merge operation. Column headers don't need to match between tables. However, it's important to note that the columns must be of the same data type, otherwise the merge operation might not yield correct results.
You can also select multiple columns to perform the join by selecting Ctrl as you select the columns. When you do so, the order in which the columns were selected is displayed in small numbers next to the column headings, starting with 1.
For this example, you have the Sales and Countries tables. Each of the tables has CountryID and StateID columns, which you need to pair for the join between both columns.
First select the CountryID column in the Sales table, select Ctrl, and then select the StateID column. (This action shows the small numbers in the column headings.) Next, perform the same selections in the Countries table.
Screenshot of the merge dialog with the Left table for merge set to Sales, with the CountryID and StateID columns selected, and the Right table for merge set to Countries, with the CountryID and StateID columns selected. The Join kind is set to Left outer.
Note
When selecting multiple columns for a join, the order you select the columns in each table must match. For example, the first column selected in the left table is matched with the first column selected in the right table, and so on. Otherwise, you'll observe incorrect join results.
When selecting the left and right table in the Merge dialog, you can check the light bulb icon on the top right corner of the dialog to see if there are any suggestions when it comes to column-pair-mapping between both tables.
There can be many suggestions in place but, for this scenario, there's only one suggestion by the feature, where it maps the column CountryID from the Sales table to the CountryID column from the Countries table. You can select it and the column-pair-mapping is automatically applied to your dialog.
Note
Only the column-pair-mapping is suggested. Other options in this dialog, such as the join kind or fuzzy matching configuration, are out of scope for this suggestion.
After you select OK in the Merge dialog box, the base table of your query has all the columns from your left table. Also, a new column is added with the same name as your right table. This column holds the values corresponding to the right table on a row-by-row basis.
From here, you can choose to expand or aggregate the fields from this new table column, which are the fields from your right table.
Note
Currently, the Power Query Online experience only provides the expand operation in its interface. The option to aggregate will be added later this year.
A join kind specifies how a merge operation is performed. The following table describes the available join kinds in Power Query.
Join kind | Icon | Description |
---|---|---|
Left outer | All rows from the left table, matching rows from the right table | |
Right outer | All rows from the right table, matching rows from the left table | |
Full outer | All rows from both tables | |
Inner | Only matching rows from both tables | |
Left anti | Only rows from the left table | |
Right anti | Only rows from the right table |
You use fuzzy merge to apply fuzzy matching algorithms when comparing columns, to try to find matches across the tables you're merging. You can enable this feature by selecting the Use fuzzy matching to perform the merge check box in the Merge dialog box. Expand Fuzzy matching options to view all available configurations.
Note
Fuzzy matching is only supported for merge operations over text columns. For more information, go to How fuzzy matching works in Power Query.
Training
Module
Write multi-table queries by using Kusto Query Language - Training
Learn how to write Kusto Query Language (KQL) queries to combine and retrieve data from two or more tables by using the `lookup`, `join`, and `union` operators.