Create a new table in Power Bi using previously imported tables joining two tables

Question

How can I create an intermediary table using Query editor by joining two tables in Power BI.

There are two tables Person and Email which have a common Key of PersonID.

Person PerrsonID Name LastName

Email PeronID EmailID Email

Please let me know if you require any additional information.

Answer 1

First, start Power Query Editor by clicking Transform data button in the ribbon:

It will open a new window, where you will see the list of your tables (called Queries). Click Combine button:

There are two options - Append Queries and Merge Queries. Each of them has two options (the dropdown next to the menu item) - as new or not. If you choose as new, the result will be a separate new table. Otherwise the first table will be transformed by this operation.

Append Queries will combine the tables in such a way, that the result will have all the columns from both tables, but the rows will be appended in the result (like union all in SQL). In your case, you should use Merge Queries - it will combine rows from both tables into a single row (like join in SQL). So make sure Person and Email tables are selected in the combo boxes and click on PersonID column in both to define the key:

Join Kind should be Inner, or if you want to also show persons without e-mail addresses Left Outer (assuming Person is the top table). It will create a new column Email of type Table. Click on the button in the column's title to expand it:

Select which columns to be included in the result (PersonID is not needed, because it will be a duplicate, EmailID probably also can be excluded):

The result will be what you ask for. The new columns will have a prefix or suffix, but you can double-click the column title and rename it: