Forum Discussion
Merge 2 tables into one table
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
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.
2 Replies
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
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.
