Merging and Appending Data Tables

[newpbiuser01]

Hello,

I am trying to merge and append data from two tables where the first one has an employee name, ID and their travel to and from information. The second table has the employee location - city and country. This location has no bearing on the  To and From country data. 

Travel Data:

Name	ID	Department	To	From
Bob	1	Sales	CA	US
Bob	1	Sales	US
Andy	2	Marketing	CA	UK

Employee Location:

Name		City	Country
Bob	1	London	United Kingdom
Bob	1	Brisbane	Australia
Bob	1	New Delhi	India
Bob	1	Berlin	Germany
Andy	2	Boston	United States
Andy	2	Paris	France

The resulting table should merge the Travel and the Employee location data and Travel Data and if needed add rows for each employee as needed:

Name	ID	Department	To	From	City	Country
Bob	1	Sales	CA	US	London	United Kingdom
Bob	1	Sales	US		Brisbane	Australia
Bob	1	Sales			New Delhi	India
Bob	1	Sales			Berlin	Germany
Andy	2	Marketing	CA	UK	Boston	United States
Andy	2	Marketing			Paris	France

I am able to merge the data, but I can't add the new rows where the country to and from fields remain blank for the newly added rows. Would anyone know how I could do this?

Thank you for your help!

[amitchandak · Accepted solution]

@newpbiuser01 , Add nested index in both tables as suggested by Curbal - https://www.youtube.com/watch?v=7CqXdSEN2k4

Then Merge, in the merge. Select two column with CTRL select first ID and then Index in both tables and do left join with first table

merge: https://radacad.com/append-vs-merge-in-power-bi-and-power-query

[newpbiuser01]

Thanks @amitchandak that worked like a charm!