Query
  1. Merge Two Tables With Power Query
  2. Merge Tables Power Query Power Bi

In a previous post, I explained how to parse a semi-structured flat file with the help of a range-join. When you just have a few thousand lines in your semi-structured flat files, this is working really well but when your files is going bigger and bigger, this strategy starts to collapse and is really slow. Hopefully, Chris Webb explained in recent blog post how adding a primary key to a table could help to improve the performances of a merge. I wanted to test that on my set this specific case.

Merge in Power BI in Power Query Editor. The second part of the exercise is to find colors based on Item ID for your shiny new appended table. We will use the previous result query “Appended Tables” as input for the next steps. So let’s locate the Merge button. Again, click the little triangle to see what options you have. In case the headers are different, Power Query will combine and create all the columns in the new table. If the data is available for that column, it will be shown, else it will show null. You May Also Like the Following Power Query Tutorials: Combine Data from Multiple Workbooks in Excel (using Power Query). Merge Tables using Inner and Anti Joins in Power Query Posted on December 23, 2015 by Ken Puls Last week’s blog post on merging tables using Outer joins has proved to be pretty popular.

I started to work on my previously created table categories that was in this state.

I could have created a primary key on this table with any column but I’ve serious doubt that it would result in any improvement of performances. Main reason is the usage of a custom range-join and not a merge provided by the M language. To work-around that I decided to change the structure of my table and use a classical merge function. In place of having one row for each category with the bounds of this range as fields, I want to have one row for each value between the bounds. My table will be larger but I’ll be able to use a traditional merge. The final result is the following:

To achieve that result, I created a function to generate a list of numbers contained between the lower and the upper bound given by the two fields Start Range and End Range. I added this list as a new column of my existing table.

then, I expanded the list as new rows

And to be sure that a primary key was added to my table, I added a step to remove the duplicates based on my new 'list' column.

Then on my main table participants I replaced thejoin-range by a native merge (Merge queries in the UI or NestedJoin in the code) using this new column.

And you know what? I'm happy … performance has improved from a laborious 'more than 3 minutes' to a wonderful 'less than 15 seconds'.

-->

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.

Merging queries

You can find the Merge queries command on the Home tab, in the Combine group. From the drop-down menu, you'll see two options:

  • Merge queries: Displays the Merge dialog box, with the selected query as the left table of the merge operation.
  • Merge queries as new: Displays the Merge dialog box without any preselected tables for the merge operation.

Identify tables for merging

The merge operation requires two tables:

  • Left table for merge: The first selection, from top to bottom of your screen.
  • Right table for merge: The second selection, from top to bottom of your screen.

Note

The position—left or right—of the tables becomes very important when you select the correct join kind to use.

Select column pairs

After you've selected both the left and right tables, you can select the columns that drive the join between the tables. In the example below, there are two tables:

  • Sales: The CountryID field is a key or an identifier from the Countries table.
  • Countries: This table contains the CountryID and the name of the country.

Merge dialog box 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.

Merge Two Tables With Power Query

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 will show the small numbers in the column headings.) Next, perform the same selections in the Countries table. The following image shows the result of selecting those columns.

![Merge dialog box 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.

Expand or aggregate the new merged table column

After selecting OK in the Merge dialog box, the base table of your query will have all the columns from your left table. Also, a new column will be 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 will be the fields from your right table.

Table showing the merged Countries column on the right, with all rows containing a Table. The expand icon on the right of the Countries column header has been selected, and the expand menu is open. The expand menu has the Select all, CountryID, StateID, Country, and State selections selected. The Use original column name as prefix is also selected.

Note

Merge Tables Power Query Power Bi

Currently, the Power Query Online experience only provides the expand operation in its interface. The option to aggregate will be added later this year.

Join kinds

A join kind specifies how a merge operation will be performed. The following table describes the available join kinds in Power Query.

Join kindIconDescription
Left outerAll rows from the left table, matching rows from the right table
Right outerAll rows from the right table, matching rows from the left table
Full outerAll rows from both tables
InnerOnly matching rows from both tables
Left antiOnly rows from the left table
Right antiOnly rows from the right table

Fuzzy matching

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.

Coments are closed

Most Viewed Posts

  • File Stream Install
  • Christmas Movies Amazon Prime
  • Ssrs Join Two Datasets
  • Mail Merge Data

Scroll to top