Before to import the data, sometimes, I need to remove, to add, etc values including to combine only existing values. I can do those things in the Power Query Editor, let’s take an example, I have those 2 tables:

Table1	Table2

I want to merge the table2 into table1, I have 2 choices: add rows or columns. For the rows, I will select table1 then click on “home -> append queries”:

On the popup, I select table2 in the “table to append” field:

As we can see, the “ticket” column of table2 is not in the same column as “number” of table1. If it is what I want, perfect but if I want them in the same column, before to append, I need to change the name from “ticket” to “number” then append them:

NOTE: to remove duplicates, click on “home -> remove rows -> remove duplicates”

For the columns, I will select table1 then click on “home -> merge queries”:

Then on the popup, I will select the “number” column and the “ticket” column:

Now, I have to select one of the options in the “join kind” field so from here, there are different scenarios that I can do:

Merge by matching values of table1

Select this option:

Click on the “expand” icon of the new column to unselect the “ticket” (to avoid duplicate with the “number” column) and “use original column name as prefix” (this is optional, I can keep it selected) columns:

In the final result, I can see that the INC012 of the table2 is not appearing because in table1, it doesn’t exist.

Merge by matching values of table2

Select this option:

For this option, I will keep the “ticket” and the “status” columns selected:

I will remove the “number” column by clicking on “file -> choose columns” to avoid the duplication with the “ticket” column:

Merge by matching values of both tables

Select this option:

Merge by keeping data of matching values

Select this option:

Merge by removing data of matching values

Select this option:

Merge by keeping data of distinct values

Select this option:

I can get the same result using formula, read Power BI: extract columns from a table to another one.