Merge 2 tables into one table

[Alessandro-laba]

Hi all, 

I have 2 different datasets uploaded in Power Bi as 2 different tables. These 2 tables are quite different but the unique identifier is the email address. However sometimes a contact is in one table but not in the other. What i would like to get is to merge the 2 tables into 1 and get all the fields from both tables.

There is an example below and the desired outcome.

Is this something possible in Power Bi?

Thanks,

Ale

[v-cazheng-msft · Accepted solution]

Hi @Alessandro-laba 

You can create a new pbix file with these two tables(Merge and NATURALLEFTOUTERJOIN don't support tables from Power BI datasets) and try these steps.

1 Remove ‘company’ column of Table 2 in Power Query

2 Rename the ‘email address’ column of Table 1(NATURALLEFTOUTERJOIN doesn’t support Duplicate column names used for join)

3 Create a Calculated table with NATURALLEFTOUTERJOIN function

Table =

VAR join_table =

    NATURALLEFTOUTERJOIN ( Table2, Table1 )

RETURN

    SELECTCOLUMNS (

        join_table,

        "first name", [first name],

        "last name", [last name],

        "company", [company],

        "Name", [Name],

        "country", [country],

        "email address", [email address]

    )

The result looks like this:

For more details, you can refer the attached pbix file.

Best Regards

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[VijayP]

@Alessandro-laba 

In Power Query you can use Merge Queries option to get this result

First Add these two tables to Power Query .

Using the similar column , Merge the tables and expand to the result