Merging (joining) tables -PowerQuery basics-

In this article, we will solve such problems!

As a sample, let's add product production area information to the following sales data.

The country of origin information for each product is imported into a separate table as a "country of origin" query.

With the "Sales_Daily" query selected as the source,
select "Home" from the menu above, click the "Merge Queries" icon, and select "Merge Queries"

The "Merge" settings screen will appear.
Confirm that the query selected in the upper row (at ①) is "Sales_Daily" .

Next, select "Production area" for the query (at ②) in the lower row.
Finally, select the column that will be the key for merging (joining) . This time, he wants to merge (combine) the products with a key, so he selects each "product name".

For "Join Type", select "Left Outer" . Select this when you want to add the query information from ② to the rows with matching keys while leaving all rows from the query from ①. Other options will be explained separately, so I will omit them here.

Click "OK" and you will see that the "Country of Origin" column has been added to the "Sales_Daily" query .

No information is displayed in the "Country of Origin" column yet, so configure what information to display.
Click the expand icon at the right end of the "Country" column to display a list of column names for the "Country" query .

In the "Country of origin" query, select the checkbox for only the columns you want to display and click "OK" . This time, we only want to display the "Country of Origin" column, so turn on the check box only for "Country of Origin".

Click "OK" and you will see that the "Country" column has been added .

On the "Merge" setting screen, if you want to merge (combine) multiple columns into a key, you can select multiple columns by clicking while holding down the "shift" key . At this time, the numbers will be displayed in the selected order as follows. Matching of values ​​will be checked for columns with matching column numbers selected in both the upper and lower rows .

Let me explain what will happen if there are multiple rows for one key in the "locality" query . For "apples", we will set two production areas: Aomori and Nagano.

Immediately after merging (combining), there is no change, but…

When you "expand" the sales information for number 1 has increased to two lines. However, if you look at the row of production areas, Aomori and Nagano are different. Be careful when merging (joining) queries that have multiple rows for a single key in this way, as data may be unexpectedly duplicated.

Let me explain what will happen if there is no corresponding row for the key in the "locality" query .
Assume that there is no row for "apple" in the "locality" query.

Immediately after merging (combining), there is no change, but…

When you "expand" the sales information for "apples," you can see that "null" is displayed in the "Country of Origin" column, indicating that there is no information .